In a cell I have a list of locations made with data validation. Example Budapest, Belgrade, Bucharest. next to the cell I have formula that i have to change manually if i change the cell with the location, EX: =index( Budapest!I:I; max(if(Budapest!B:B=E127; row(Budapest!B:B)))), =index( Belgrade!I:I; max(if(Belgrade!B:B=E127; row(Belgrade!B:B))))...etc

Is it a way to change automatically the formula, for ex if I select the city Budapest the formula to change to Budapest.

I have tried: =if(F127="Budapest"index( Budapest!I:I; max(if(Budapest!B:B=E127; row(Budapest!B:B))))) if(f127="Belgrade"index(Belgrade!F:F; max(if(Belgrade!B:B=E127; row(Belgrade!B:B)))))

CodePudding user response：

if A1 = Bucharest (or whatever else) use:

=INDEX(INDIRECT(A1&"!I:I"); MAX(IF(INDIRECT(A1&"!B:B")=E127; ROW(INDIRECT(A1&"!B:B")))))

CodePudding user response：

Depending on where the input data is will determine what the best formula to use it. But to be vague, the answer is yes. It will most likely require you to use a formula that pulls data and instead of the putting in a location of where to pull from, put a cell reference. Some formulas wont let you use a cell’s contents to reference a location. In that case you can concatenate the string from a cell into the formula. Here is an example of concatenation within a formula that does not allow cell references (query select where).

=QUERY('tab1'!$A$4:$R, "select * where A = '"&$C$5&"'")

It pulls all data where column A matches whatever value is typed into cell C5.

I understand this formula does not match your data but it provides an explanation of the mechanism of how to do so. I hope it helps. If not, please provide an example sheet and I can create a specific formula for your situation.