Import data from different sheets based off one cell

[ThayerT]

Hello,

I've got a nutrition calculator that I want to be able to have auto fill with the nutrition facts from another sheet. Kicker is I have multiple sheets with different recipes. So I need to be able to select a recipe from my drop down list (b2 on my ingredients tab), then have the nutrition fact generate from that recipe's tab.

[#2]

I figured out this formula =IFS(Biscuits!B1=B2,Biscuits!B2:I12,Test!B1=B2,Test!B2:I12,TRUE,"") which works. If there is better way let me know. I would rather not have to add to the formula every time I add a recipe.

[#3]

@ThayerT 

Your approach using the IFS function works, but it requires manually updating the formula each time you add a new recipe tab. Instead, you can use the INDIRECT function combined with the VLOOKUP function to dynamically reference the appropriate recipe tab based on the selection in cell B2.

Here is how you can do it:

1. Create a table in each recipe tab with the nutrition facts. The first column should contain the item names, and the subsequent columns should contain the corresponding nutrition facts.
2. In your ingredients tab, use the following formula to dynamically reference the selected recipe tab and retrieve the nutrition facts:

=IFERROR(VLOOKUP(A2,INDIRECT("'"&$B$2&"'!A:I"),COLUMN(B2),FALSE),"")

In this formula:

• A2 is the item name in your ingredients tab.
• $B$2 contains the name of the selected recipe tab.
• INDIRECT("'"&$B$2&"'!A:I") dynamically generates the reference to the selected recipe tab.
• VLOOKUP searches for the item name in the selected recipe tab and retrieves the corresponding nutrition fact from the specified column.
• COLUMN(B2) dynamically adjusts the column index in the VLOOKUP function as you drag the formula across columns in your ingredients tab.
• IFERROR handles cases where the item name is not found in the selected recipe tab.

With this setup, you won't need to manually update the formula when you add new recipe tabs. Just ensure that the item names are consistent across all recipe tabs. The text was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

[#4]

@ThayerT The fact that "Biscuits!B2:I12" works for you, means that you can spill an entire range of cells in one go. Add the fact that you also seem to use structured tables to hold your recipes, you may want to try the following.

Add a Total column to each recipe table and give each table a name that is equal to one that you will include in the drop down list.

Now you can return the entire table in one go based on the value in B2 and it doesn't matter where the table sits in your workbook as Excel looks for a table with that specific name.

=INDIRECT(B2&"[#All]")