Select Odd Rows from Named Range

My situation: I have a column of 100 cells listing 50 nutrients (vitamins, etc.), two cells per nutrient. I want to use the first of each pair in a macro. The 100 cells I have named “BaseRng”, and it is dynamic because the list may change. I’ve been struggling to find a formula that will select out the odd-numbered cells in BaseRng, and give it a name that will work in a macro. I’ve found several that work beautifully as worksheet formulas (I stick it in a cell and it returns the values in the 1^st, 3^rd, 5^th etc. cells). But when I put the formula in the “Refers to:” field in the New Name window (with absolute referencing), and then type the name in the name box on the worksheet, sometimes nothing happens. The best I get is the 1^st cell in BaseRng is selected. Why is this happening, and how can I name all the odd-numbered cells in BaseRng? I’m trying to avoid OFFSET because of the volatility.

Following are some formulas I’ve tried on a smaller, experimental range of ten cells. They all work as worksheet formulas. As definitions for a range name, the first one selects only the first cell; the other two do nothing.

=INDEX(BaseRng,SEQUENCE(5,,,2)) I like this one. It’s compact and independent of row numbers.

=IF(ISODD(ROW(BaseRng)), BaseRng,"")

=IF(MOD(ROW(BaseRng),2)=0, BaseRng,"")

I’ve spent hours searching the internet, to no avail. Please, I would be very grateful for any help. Thank you.

Bravo, Carim! Thank you so much for your quick response. I didn’t know a macro could be used in that way. However, I see in Name Manager that the resulting definition is a series of individual cell references. I already have that. After Excel adds a sheet name to all fifty references, I have a definition 1,410 characters long. (I wish I had your macro when I created that range name. It would have saved me time.) And it’s not fully dynamic. If I resize or move BaseRng, I have to remember to run the macro to update the new range name. That’s no great hardship, of course, but I was hoping to have all my range names dynamic. In any case, I’m still baffled as to why the concise formulas I mentioned, when entered into a cell, can find the correct cells and return their values but cannot find those same cells when used as a name definition. Other formulas that I’ve used in the past do both perfectly. I’d like to know for future reference so I can be more self-reliant using Excel. If it’s not possible, so be it; I can live with it. But I don’t want to miss out because I gave up too soon.

Many thanks for your expertise and time. I will add your macro to my library of valuable procedures.

Thanks again for your fast response.

Sorry if I wasn’t clear. BaseRng is already dynamic. I am using this definition;

='Nutrients Consumed'!$BE$21:INDEX('Nutrients Consumed'!$BE$21:$BE$400,MAX(IF('Nutrients Consumed'!$BE$21:$BE$400<>"", ROW('Nutrients Consumed'!$A$1:$A$380))))

I’m trying to avoid OFFSET because I’ve read that it can slow down a workbook. Does this volatility occur with range names and macros, or is it an issue with worksheet formulas only? I can post this question in a new thread if you think I should.

Regarding your last paragraph, I need only one range extracted from BaseRng, the odd cells. An array formula doing that is exactly what I’ve been trying to find. I don’t need this new, odd-cells formula to be dynamic (updating automatically when new data is added) because it is based on BaseRng, which is already dynamic. I’ve tried quite a few. After entering the formula into the “Refers to:” field in the New Name window, I enter CSE. It doesn’t add the curly brackets. And when I try to add them with the keyboard, Excel changes it to ="{=INDEX(BaseRng,SEQUENCE(5,,,2))}", with the quotation marks and the additional equals sign. Neither of these correctly select all the odd cells when I enter the name in the name box. I’m beginning to think that Excel Names will not accept or recognize array formulas.

The structure of the workbook is this; numeric data is entered into cells on the first sheet. An event procedure causes a calculation with the numbers and a small database on the second sheet. Excel then enters the results of those calculations into the appropriate cells to the right of the odd cells in BaseRng on the third sheet. If it will help, I can create a small sample workbook that may illustrate what I’m trying to explain.

So, to summarize, if you can help me do what you said in your last sentence, have an array formula (used as a range name definition) that will select out the odd cells in BaseRng (remembering that BaseRng is dynamic, and I only need one new formula), that would be a major step toward finishing this project. Thank you so much for your time and effort on this.

Carim, I was afraid of that. To be sure, do you mean that all array formulas will not operate for a Named Range definition, or just the one I’m trying to use?

The formula you sent is a little beyond my skill level, so I’ll have to spend some time studying it. But for now, thank you for your valiant effort. I’ll try to make contact again in a day or so.

Rory – I just opened up Ozgrid and saw your post as I was copying and pasting the following which I set up in a Word document. I agree; it’s a good idea.

Hello, again. I think I have a solution, inspired by your macro. After looking at it again, it occurred to me to put something like it in my own macro. Here is the original action part of my macro:

Dim r As Range
indx = 1
For Each r In Worksheets("Nutrients Consumed").Range("odd-cells named range").Offset(0, j)
r.Value = r.Value + (arstrt(1, indx) / 100) * NewValue
indx = indx + 1
Next r

r and k are the only variables pertinent to our concern; the others have to do with where the data comes from. Here is the modification:

Dim r As Integer
Dim k As Range
Set k = Worksheets("Nutrients Consumed").Range("BaseRng")
For r = 1 To k.Rows.Count Step 2
k.Cells(r, 1).Offset(0, j).Value = _
k.Cells(r, 1).Offset(0, j).Value + (arstrt(1, indx) / 100) * NewValue
indx = indx + 1
Next r

In other words, instead of looping through every cell in the new odd-cells named range, I simply loop through every odd cell in BaseRng and skip the named range altogether. Any suggestions for improvement are welcome. Also, for future reference I’d still like to know if all array formulas will not operate for a Named Range definition, or just the one I was trying to use and why.

PS - Carim

Rory – I just opened up Ozgrid and saw your post as I was copying and pasting the following which I set up in a Word document. I agree; it’s a good idea.

Hello, again. I think I have a solution, inspired by your macro. After looking at it again, it occurred to me to put something like it in my own macro. Here is the original action part of my macro:

Dim r As Range

indx = 1

For Each r In Worksheets("Nutrients Consumed").Range("odd-cells named range").Offset(0, j)

r.Value = r.Value + (arstrt(1, indx) / 100) * NewValue

indx = indx + 1

Next r

r and k are the only variables pertinent to our concern; the others have to do with where the data comes from. Here is the modification:

Dim r As Integer

Dim k As Range

Set k = Worksheets("Nutrients Consumed").Range("BaseRng")

For r = 1 To k.Rows.Count Step 2

k.Cells(r, 1).Offset(0, j).Value = _

k.Cells(r, 1).Offset(0, j).Value + (arstrt(1, indx) / 100) * NewValue

indx = indx + 1

Next r

In other words, instead of looping through every cell in the new odd-cells named range, I simply loop through every odd cell in BaseRng and skip the named range altogether. Any suggestions for improvement are welcome. Also, for future reference I’d still like to know if all array formulas will not operate for a Named Range definition, or just the one I was trying to use and why.

Sorry, everyone. I forgot to push "reply" for the above. The following I just wrote. Thanks for your patience.

Hi, Carim. Attached is a small imitation model of my project that you asked for. I would like to keep the real information under wraps for now, so this is a pretend tool for calculating genetic content of one drop of blood from various animals. (This is the phoniest science you’ll encounter this week.) Numbers in “Input” are multiplied by numbers in “Data”, and the results are displayed in the appropriate cells in “Genetic content”. The message boxes are for testing and not part of the final workbook. Let me know if anyone sees where I could make improvement.

I tried copying your formula (from post #9) to a cell, and it returns only the first value. The formula from post #12 returns all the correct values (horizontally), but I can’t get it to work in a range name.

Thanks, Pecoflyer, for your post. Sorry, but I just don’t understand arrays yet. I’ve read several times that CSE is no longer necessary. But I read just as often that it is. In any case, I want others to be able to to use this on their own machines. And what is AFAIK? Thanks for you help.

And thank you, Rory. The idea is working beautifully. But I’m not sure what you mean in #18. It’s been my experience that many formulas, when used on a worksheet, return values. When those same formulas are used as range name definitions, they identify the ranges those values occupy.

Excel forum question sample NIC.xlsm