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 1st, 3rd, 5th 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 1st 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.
I’ve spent hours searching the internet, to no avail. Please, I would be very grateful for any help. Thank you.