Range of cells until first blank cell (formula not vba)

malcolmlo,

Welcome to the forum!
If this is for a named range, you can use this formula. Adjust the row numbers to suit. I chose 65000 because I don't know what would realistically be your last line of data. The fewer rows it has to check, the more efficient the formula will be:

Please Login or Register  to view this content.

If this is not for a named range, you can use the same formula but you will have to array-enter the formula with Ctrl+Shift+Enter and not just Enter.

It works for me when I test it. At this point I'd have to see a sample workbook so...

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
To attach a file, click the "Go Advanced" button and then click the paperclip icon to open the Manage Attachments dialog.

Ah, ok. That is because you can't use Indirect with a dynamic named range. The named range is working, you can test that by doing:

Please Login or Register  to view this content.

That will return 3, which is correct. There are 3 rows in the named range Arizona. However, you can't do:

Please Login or Register  to view this content.

Because "Arizona" is a dynamic named range which is defined with a formula.

Basically, what you'll have to do is used a data validation formula and put all of the information on the same sheet. The information columns can be hidden if desired.
Attached is a modified version of your example workbook.
There is now only 1 sheet, Sheet1.
Row 1 is used as a header row so data starts in row 2.
Starting in Column E is the list of states with their cities below.

I created a dynamic named range formula named list_States which is defined with the following formula:

Please Login or Register  to view this content.

Now, in cell A2 and copied down is this data validation list formula. This works because we're not using Indirect, we're using the named range directly:

Please Login or Register  to view this content.

Lastly, in cell B2 and copied down is this data validation list formula:

Please Login or Register  to view this content.

The cell $DA$1 is used only as a blank cell. So if nothing has been chosen for the state, the City drop-down list is blank. The Offset and subsequent functions after that will get the correct city list based on the chose state. Note this only works if the data is on the same sheet as the data validation drop-down list as stated earlier.

OK I have worked on this now and am still having problems...I have to update all your cell references with the correct references in my sheet which are different. Maybe I can define what each cell is in my sheet:

Starting cell of horizontal dropdown list: DZ2
Ending cell of horizontal dropdown list: FH2
Blank Cell: DY1
Dropdown validation cell A (independent): M3
Dropdown validation cell B (dependant on A): N3
Sheet name: quests


Your formula:
=Sheet1!$E$1:INDEX(Sheet1!$E$1:$BA$1,0,MATCH(TRUE,Sheet1!$E$1:$BA$1="",0)-1)

My Modified formula (doesnt work):
=quests!$DZ$2:INDEX(quests!$DZ$2:$FH$2,0,MATCH(TRUE,quests!$DZ$2:$FH$2="",0)-1)

Your formula:
=IF($A2="",$DA$1,OFFSET($D$1,1,MATCH($A2,$E$1:$G$1,0),COUNTA(OFFSET($D:$D,,MATCH($A2,$E$1:$G$1,0)))-1))

My Modified formula (cant test until first one works):
?? I dont understand the references to D1, there is nothing in this cell and you already used a different cell for the "blank cell". Do i reference the blank cell in front of the start of the dropdown list? I dont have one at the moment but I can make one...

Thank you! Hope this explains enough Otherwise I may just have to send you the actual file if this doesnt work, heheh.

On your modified formula, are there any blank cells in DZ2:FH2? If there are no blank cells, the formula will fail. You can correct this by extending the formula to include a blank cell at the end so that the range is DZ2:FI2. However, if that range will never grow or shrink, there's really no need to use a dynamic named range formula, in which case you would just use =quests!$DZ$2:$FH$2.

As for the D1, that is the cell to the left of of E1:G1 in my example workbook. The offset formula requires that I use that cell because I will be matching $A2 against E1:G1, which will return a number signifying how many cells to the right of D1 to use. If the match is in E1, that is the first cell so the function returns a 1. D1 offset to the right 1 becomes E1, so now it is utilizing the correct column. I hope that makes sense.

OK it does make sense, let me try these and get back to you.