Xlookup picking the sheet name from a dropdown list

[philxyz]

Hi
I am trying to use a XLOOKUP like below but instead of having the sheet name, I want to pick up the sheet name from a drop down list.
The drop down list has multiple sheet names in a file. So I want to be able dynamically use the Xlookup formula on different sheets.
Thanks
=XLOOKUP(A7;Sheet1!$B$2:$BF$2;Sheet1!$B$3:$B$20;;0;1)

 

[ETAF]

not quite sure how that works - the lookup range is B2 to BF2 - so horizontal list
and the return is B3 to B20 - vertical list

anyway
and INDIRECT should do that
=XLOOKUP(A7,INDIRECT(A2&"!$B$2:$BF$2"),INDIRECT(A2&"!$B$3:$BF$3"),,0,1)

replace commas with semicolon as i'm in UK and use commas
=XLOOKUP(A7;INDIRECT(A2&"!$B$2:$BF$2");INDIRECT(A2&"!$B$3:$BF$3");;0;1)


may not need the 2 indirects() , i cannot seem to get to work without - other members may have a better solution

 

[philxyz]

Sorry I used a bad example, this is more like what I wanted to do.
So I have a dropbox with Sheet Names - APAC, US, EU
I want to change to dropdown so the xlookup performed on different sheets


=XLOOKUP($C4,APAC!$B:$B,APAC!C:C)/XLOOKUP($D4,APAC!$B:$B,APAC!C:C)

 

[ETAF]

you are not fixing the c column, with a $
and you seem to have , now
you should be able to use
=XLOOKUP(A7,INDIRECT(A2&"!$B$2:$BF$2"),INDIRECT(A2&"!$B$3:$BF$3"),,0,1)
substitute in your formula
=XLOOKUP($C4,APAC!$B:$B,APAC!C:C)/XLOOKUP($D4,APAC!$B:$B,APAC!C:C)

=XLOOKUP($C4,indirect(dropdowncell&"!$B:$B"),indirect(dropdowncell&"!C:C"))/XLOOKUP($D4,indirect(dropdowncell&"!$B:$B"),indirect(dropdowncell&"!C:C"))

BUT using full column references - as excel may have over 1 million rows depending on version can slow things down - so it may be worth using a range that easily covers the data likely to be needed

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

 

[philxyz]

Thanks @ETAF I've attached a file.
I've applied your formula, it picks up the dropdown list now, thanks.
I'll update the final version so it's not full column references.
The remaining issue is if I copy the formula to the right, the relative part of the formula acts as if it absolute, the column reference is not changing.

 

[ETAF]

no the column reference will not changes as its enclosed with quotes - may have to do something clever with address() and column() dependin on the reference needed
or maybe sumproduct - so it uses the header
but E2 is fixed in your example $E$2