Geek asks and answers

Excel Data Validation Data Source

I tried searching, but maybe I just could not find the right combination of keywords and could not find a solution.

What I'm trying to do in Excel is to create a dynamic checklist based on the key value entered in another cell.

I have 3 sheets: WSName (Columns)

  • Magazine (buildings, floors)
  • Buildings (BuildingId, BuildingName)
  • Floors (FloorId, BuildingId, FloorName)

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

I created separate named ranges for all floors by creating "Floor # BuildingId #"

I want that when “Building” is selected on the Sheet sheet, the list of floors gets dynamically populated with available floors for this building on the Flooring sheet.

My thought was at the source of the data validation I would have to do:

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

And it partially works, but always returns the same list of floors, because $ A $ 2 is not dynamic depending on its row, 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 message.

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

+5
source share
2 answers

You are using the wrong syntax - what a manual link looks like:

='Sheet 1'!A1

Add an exclamation and ', and you're done. The rest seems good to me)

+2
source

Ok, this works for me, I just set the data validation source to:

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

, , A2 , .

@Peter L. !

+1

More articles:


All Articles