Forum Discussion

ThayerT's avatar
ThayerT
Copper Contributor
Apr 13, 2024

Import data from different sheets based off one cell

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 ...
excel
Formulas and Functions
ThayerT's avatar
ThayerT
Copper Contributor
Apr 13, 2024

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.

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Apr 14, 2024

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]")

 

 

 

Resources