Excel Data Validation Source

Question

I've tried searching, but maybe just haven't found the right combination of key words and have been unable to find a solution.

What i'm trying to do in Excel is generate a dynamic validation list based on a key value entered in another cell.

I have 3 worksheets: WSName(Columns)

1. Log (Buildings, Floors)
2. Buildings (BuildingId, BuildingName)
3. Floors (FloorId, BuildingId, FloorName)

I have created a named range for all my building ids called "BuildingIds"

I have created separate named ranges for all floors by building called "Floor#BuildingId#"

What I want is, when a Building is selected in the Log worksheet, the Floor list gets dynamically populated with the available floors for that building from the Floors worksheet.

My thought was in the Data Validation Source I should be able to do this:

=INDIRECT("Floors"&$A$2)

And it partially works, but always returns the same floors list because $A$2 isn't dynamic depending on the row it's in, so I need $A$2 to use the value from the current row.

This returns the value of the selected building that I want for the current row:

=INDIRECT("A"&ROW())

But when I try:

=INDIRECT(CONCATENATE("Floor","A"&ROW()))

OR

=CONCATENATE("Floor",INDIRECT("A"&ROW()))

I get an error.

Can you help me with this formula please?! Thanks in advance!

Answer 1

You use incorrect syntax - that's how manual link looks like:

='Sheet 1'!A1

Add exclamation and ' and you're done. The rest seems fine for me)

Answer 2

Ok, I've got this working, I simply set the Data Validation Source to:

=INDIRECT(CONCATENATE("Floors",A2))

Then when the formula was copied over the entire column, the A2 updated to the current row as expected.

@Peter L. Thanks for your help!