Search through multiple Tabs

[d7882]

Hello everyone!

I was hoping someone could help me figure this trick out.

So I need to find a number by searching through multiple tabs in one excel file.
I have a sample excel file attached. Essentially, I would like to find the data by using Income and Asset 1 and search through the tabs.

I hope this makes sense. Please let me know if I need to clarify anything.

Thank you very much

[AliGW]

Please fill this in manually to show what you are trying to do:

Excel 2016 (Windows) 32 bit

	C	D	E	F
7		Income	Costs	Net Income
8	Asset 1
9	Asset 2
10	Asset 3

Sheet: Main

Will there only ever be one asset per tab? Or is this workbook massively over-simplified?

[jeffreybrown]

Here is one way

D8 copied across and down >> =INDEX(INDIRECT("'" &$C8 & "'!$D$4:$D$6"),MATCH(D$7,INDIRECT("'" &$C8 & "'!$C$4:$C$6"),0))

For Costs you will get an error because on the Asset tabs you have Cost. It has to have the same spelling.

Note: Also, this will just take the January total. You didn't specify a month. If you are looking for the total, you could add a total in column P and then change $D$4:$D$6 to $P$4:$P$6

[AliGW]

Change E7 to Cost (NOT Costs), then this in D8 copied across and down:

=SUMPRODUCT((INDIRECT("'"&$C8&"'!$C$4:$C$6")=Main!D$7)*INDIRECT("'"&$C8&"'!$D$4:$O$6"))

[d7882]

Thanks Jeffrey - this looks like it solves my issue and yes, I changed the spelling to make it work.

Ali - it was an over-simplified file. Thanks for checking into it

[AliGW]

See the solution I offered above.

Please stop quoting unnecessarily!

[jeffreybrown]

Hi d7882,

You are very welcome and thanks for the feedback.

@Ali, thanks, great solution.