Creating A Named Range

[peakoverload]

Ahh this is driving me nuts!

I'm trying to create 3 different named ranges which are of alternate cells in columns. Each column runs from row 3 to row 106 and each named range covers 5 columns. Therefore each named range needs to contain 260 cells (52 cells per column x 5 columns).

Apart from the fact that you can't deselect a sell by Ctrl+Right Clicking on it, making a selection of the relevant cells is proving impossible.

For example I carefully selected all 260 cells for my 1st named range and entered a name. However when I then go into Name Manager I see that loads of cells haven't been selected at all and instead some cells in some columns have been but with huge gaps between them. If I try to then add cells my Ctrl+Clicking on them I find that I can add some but then one of two things will happen. I'll either click on one cell, the same cell every time, that deselects everything and I have to start all over again or when I try to save the new range I get a message saying the formula is wrong.

I've also tried typing in the names of the cells manually but the same thing happens. Annoyingly I've manually created a formula with all the cells I need in Notepad but even though the option to Paste is there, it won't let me paste into the field!

Is there a limit to the number of cells you can have in a named range?

Is there way of editing the name range accurately?

Is there a better, easier way of selecting all the cells for a named range?

For reference this is the cells I'm trying to make into a named range:

Sheet1!$E$3,Sheet1!$E$5,Sheet1!$E$7,Sheet1!$E$9,Sheet1!$E$11,Sheet1!$E$13,Sheet1!$E$15,Sheet1!$E$17,Sheet1!$E$19,Sheet1!$E$21,Sheet1!$E$23,Sheet1!$E$25,Sheet1!$E$27,Sheet1!$E$29,Sheet1!$E$31,Sheet1!$E$33,Sheet1!$E$35,Sheet1!$E$37,Sheet1!$E$39,Sheet1!$E$41,Sheet1!$E$43,Sheet1!$E$45,Sheet1!$E$47,Sheet1!$E$49,Sheet1!$E$51,Sheet1!$E$53,Sheet1!$E$55,Sheet1!$E$57,Sheet1!$E$59,Sheet1!$E$61,Sheet1!$E$63,Sheet1!$E$65,Sheet1!$E$67,Sheet1!$E$69,Sheet1!$E$71,Sheet1!$E$73,Sheet1!$E$75,Sheet1!$E$77,Sheet1!$E$79,Sheet1!$E$81,Sheet1!$E$83,Sheet1!$E$85,Sheet1!$E$87,Sheet1!$E$89,Sheet1!$E$91,Sheet1!$E$93,Sheet1!$E$95,Sheet1!$E$97,Sheet1!$E$99,Sheet1!$E$101,Sheet1!$E$103,Sheet1!$E$105,Sheet1!$H$3,Sheet1!$H$5,Sheet1!$H$7,Sheet1!$H$9,Sheet1!$H$11,Sheet1!$H$13,Sheet1!$H$15,Sheet1!$H$17,Sheet1!$H$19,Sheet1!$H$21,Sheet1!$H$23,Sheet1!$H$25,Sheet1!$H$27,Sheet1!$H$29,Sheet1!$H$31,Sheet1!$H$33,Sheet1!$H$35,Sheet1!$H$37,Sheet1!$H$39,Sheet1!$H$41,Sheet1!$H$43,Sheet1!$H$45,Sheet1!$H$47,Sheet1!$H$49,Sheet1!$H$51,Sheet1!$H$53,Sheet1!$H$55,Sheet1!$H$57,Sheet1!$H$59,Sheet1!$H$61,Sheet1!$H$63,Sheet1!$H$65,Sheet1!$H$67,Sheet1!$H$69,Sheet1!$H$71,Sheet1!$H$73,Sheet1!$H$75,Sheet1!$H$77,Sheet1!$H$79,Sheet1!$H$81,Sheet1!$H$83,Sheet1!$H$85,Sheet1!$H$87,Sheet1!$H$89,Sheet1!$H$91,Sheet1!$H$93,Sheet1!$H$95,Sheet1!$H$97,Sheet1!$H$99,Sheet1!$H$101,Sheet1!$H$103,Sheet1!$H$105,$K$3,Sheet1!$K$5,Sheet1!$K$7,Sheet1!$K$9,Sheet1!$K$11,Sheet1!$K$13,Sheet1!$K$15,Sheet1!$K$17,Sheet1!$K$19,Sheet1!$K$21,Sheet1!$K$23,Sheet1!$K$25,Sheet1!$K$27,Sheet1!$K$29,Sheet1!$K$31,Sheet1!$K$33,Sheet1!$K$35,Sheet1!$K$37,Sheet1!$K$39,Sheet1!$K$41,Sheet1!$K$43,Sheet1!$K$45,Sheet1!$K$47,Sheet1!$K$49,Sheet1!$K$51,Sheet1!$K$53,Sheet1!$K$55,Sheet1!$K$57,Sheet1!$K$59,Sheet1!$K$61,Sheet1!$K$63,Sheet1!$K$65,Sheet1!$K$67,Sheet1!$K$69,Sheet1!$K$71,Sheet1!$K$73,Sheet1!$K$75,Sheet1!$K$77,Sheet1!$K$79,Sheet1!$K$81,Sheet1!$K$83,Sheet1!$K$85,Sheet1!$K$87,Sheet1!$K$89,Sheet1!$K$91,Sheet1!$K$93,Sheet1!$K$95,Sheet1!$K$97,Sheet1!$K$99,Sheet1!$K$101,Sheet1!$K$103,Sheet1!$K$105,$N$3,Sheet1!$N$5,Sheet1!$N$7,Sheet1!$N$9,Sheet1!$N$11,Sheet1!$N$13,Sheet1!$N$15,Sheet1!$N$17,Sheet1!$N$19,Sheet1!$N$21,Sheet1!$N$23,Sheet1!$N$25,Sheet1!$N$27,Sheet1!$N$29,Sheet1!$N$31,Sheet1!$N$33,Sheet1!$N$35,Sheet1!$N$37,Sheet1!$N$39,Sheet1!$N$41,Sheet1!$N$43,Sheet1!$N$45,Sheet1!$N$47,Sheet1!$N$49,Sheet1!$N$51,Sheet1!$N$53,Sheet1!$N$55,Sheet1!$N$57,Sheet1!$N$59,Sheet1!$N$61,Sheet1!$N$63,Sheet1!$N$65,Sheet1!$N$67,Sheet1!$N$69,Sheet1!$N$71,Sheet1!$N$73,Sheet1!$N$75,Sheet1!$N$77,Sheet1!$N$79,Sheet1!$N$81,Sheet1!$N$83,Sheet1!$N$85,Sheet1!$N$87,Sheet1!$N$89,Sheet1!$N$91,Sheet1!$N$93,Sheet1!$N$95,Sheet1!$N$97,Sheet1!$N$99,Sheet1!$N$101,Sheet1!$N$103,Sheet1!$N$105,$Q$3,Sheet1!$Q$5,Sheet1!$Q$7,Sheet1!$Q$9,Sheet1!$Q$11,Sheet1!$Q$13,Sheet1!$Q$15,Sheet1!$Q$17,Sheet1!$Q$19,Sheet1!$Q$21,Sheet1!$Q$23,Sheet1!$Q$25,Sheet1!$Q$27,Sheet1!$Q$29,Sheet1!$Q$31,Sheet1!$Q$33,Sheet1!$Q$35,Sheet1!$Q$37,Sheet1!$Q$39,Sheet1!$Q$41,Sheet1!$Q$43,Sheet1!$Q$45,Sheet1!$Q$47,Sheet1!$Q$49,Sheet1!$Q$51,Sheet1!$Q$53,Sheet1!$Q$55,Sheet1!$Q$57,Sheet1!$Q$59,Sheet1!$Q$61,Sheet1!$Q$63,Sheet1!$Q$65,Sheet1!$Q$67,Sheet1!$Q$69,Sheet1!$Q$71,Sheet1!$Q$73,Sheet1!$Q$75,Sheet1!$Q$77,Sheet1!$Q$79,Sheet1!$Q$81,Sheet1!$Q$83,Sheet1!$Q$85,Sheet1!$Q$87,Sheet1!$Q$89,Sheet1!$Q$91,Sheet1!$Q$93,Sheet1!$Q$95,Sheet1!$Q$97,Sheet1!$Q$99,Sheet1!$Q$101,Sheet1!$Q$103,Sheet1!$Q$105

[peakoverload]

Just thinking that perhaps a Dynamic selection is perhaps a better way. But to be honest until 10 minutes ago I'd never heard of Dynamic Selections so waaaay out of my depth now.

I've found that this formula allows me to select everything that is added to Column E

=OFFSET(Sheet1!$E$2,1,0,COUNTA(Sheet1!$E:$E)-2,1)

This is useful as conceivably there could be times when more rows are needed. But how do I amend that formula to skip even rows and then do the same to include columns E,H,K,N,Q????

[FDibbins]

I would 1st ask why you have a need to give alternate rows a range name? If you are trying to add data on alternate rows, if the row headings have a similar wording, you may be able to use something like SUMIF()

[peakoverload]

I would 1st ask why you have a need to give alternate rows a range name? If you are trying to add data on alternate rows, if the row headings have a similar wording, you may be able to use something like SUMIF()

Probably easier if I show you.
Sample Running Scores .xlsx

So Column C,F,I,L,O all show scores achieved in a Junior National League over 5 separate rounds.

Each row in these columns records two scores for each person, the score they shot and their handicap score.

Column S shows the sum of the three highest handicap scores that each person has achieved across all 5 rounds.

Column V however I want to find the highest score shot across all 5 rounds and to also display the persons name.

So the named range is there simply to not include the handicap scores (which are always considerably higher than the actual scores) when finding the highest score and then again to find the persons name.

[FDibbins]

If you can add a helper row (which you can hide if needed), you can then put an X (or whatever you want), you can then use this ARRAY formula to pull out the top 3 for each row, no need for named ranges...
=SUMIFS($C5:$Q5,$C$1:$Q$1,"x",$C5:$Q5,">="&LARGE(IF($C$1:$Q$1="x",$C5:$Q5,0),3))