Dynamically referencing sheet names into formula

Hello!

I am trying to dynamically link data from different sheets into one master sheet. To do this, I am using the XLOOKUP formula, then referencing the row (eg. price), eg. then sheet and array where the text 'Price' can be found, then the sheet and array that the price to be called out into my master sheet. Example: -

"=XLOOKUP("Price",'Chair'!A:A,'Chair'!C:C,"-")

I have all these set up for each different row of data I want to export (eg. price, colour, type) and it does work. The issue is, when I create a new row in my master document I have to manually change the sheet name in every column. Eg I am changing 'Chair' to 'Table' to reference the different sheet titles.

It is a real pain, and I am struggling to come up with a way to automatically be able to reference to the sheet name that can dynamically be input (input once and it will change for that row of formulas).

Does anyone have any advice?

Thank you in advance.

All the best,
Beck

You would normally use the INDIRECT function for that. If you had the sheetname in a cell (e.g. X1 for illustration), then you could do this:

=XLOOKUP("Price",INDIRECT("'"&X1&"'!A:A"),INDIRECT("'"&X1&"'!C:C"),"-")

Change the cell reference as required.

Hope this helps.

Pete

Pete_UK - Thanks so much! This had actually been what I was trying, but I realise now I had added an extra space to the end of my sheetname which was causing me a lot of #REF errors. I have it now! Thank you.

Glad to hear it.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete