3d if statement, then index match

[propertymodeller]

Hi all,

I have a cash flow model I am building with over 50 assets.

Each asset tab's format is exactly the same. However, cell A1 of each asset tab has the name of the asset.

I am trying to prepare an asset summary table.

Is there a way I can do the following:

If cell A1 between Asset Tab 1 to Asset Tab 2 = "Cell A1 in the Summary tab" then

INDEX($B$8:$I$8,MATCH(1,$B$3:$I$3)) of the tab which meets the requirement above?

 

[propertymodeller]

Sorry I meant

Is there a way I can do the following:

If cell A1 between Asset Tab 1 to Asset Tab 50 = "Cell A1 in the Summary tab" then

INDEX($B$8:$I$8,MATCH(1,$B$3:$I$3)) of the tab which meets the requirement above?

 

[FDibbins]

Not really sure what you are asking here, but if you are trying to reference a sheet based on the sheet name being in a cell, you would need to use INDIRECT()

 

[propertymodeller]

I am trying to say:

If cell A1 in tab asset 1 to tab asset 50, has the text called "Office building", then

Do an index(match in that sheet that meets the criteria above.

I can apply the same index match as the formats the same across all 50 tabs, the only difference is cell A1 identifies the asset.

If i can do the above it avoids me having to go into each asset and type the index match function.

Is there a way I can upload screen shots to make the explanation easier?

 

[propertymodeller]

I am trying to say:

If cell A1 in tab asset 1 to tab asset 50, has the text called "Office building", then, make this tab the active tab and then

Do an index(match) in that sheet that meets the criteria above.

The format is the same across all 50 tabs, the only difference is cell A1 identifies the asset, that I want the index match to be completed on.

If i can do the above it avoids me having to go into each asset and type the index match function.

Is there a way I can upload screen shots to make the explanation easier?

 

[inactiveuserps07]

@propertymodeller, there's no way for me to know what you intend your formula to do. But if you actually name your asset sheets/tabs according to whatever is in A1 of that sheet/tab (easiest to remove spaces in doing so), you could use this formula in your summary sheet/tab:

=INDEX(INDIRECT(A1&"!$B$8:$I$8"),MATCH(1,INDIRECT(A1&"!$B$3:$I$3")))

In other words, rather than naming the office building sheet/tab simple 'Asset 27,' rename the sheet/tab 'OfficeBuilding,' etc.

Then use cell A1 in your summary sheet/tab to enter 'OfficeBuilding' along with the formula I provided above.

This would be more helpful to you visually as you scan across sheet/tab names; and it renders putting the description in cell A1 of each tab unnecessary.

This is certainly not the only means of achieving what you want, but it seems to me to be the easiest.

 

[propertymodeller]

@propertymodeller, there's no way for me to know what you intend your formula to do. But if you actually name your asset sheets/tabs according to whatever is in A1 of that sheet/tab (easiest to remove spaces in doing so), you could use this formula in your summary sheet/tab:

=INDEX(INDIRECT(A1&"!$B$8:$I$8"),MATCH(1,INDIRECT(A1&"!$B$3:$I$3")))

In other words, rather than naming the office building sheet/tab simple 'Asset 27,' rename the sheet/tab 'OfficeBuilding,' etc.

Then use cell A1 in your summary sheet/tab to enter 'OfficeBuilding' along with the formula I provided above.

This would be more helpful to you visually as you scan across sheet/tab names; and it renders putting the description in cell A1 of each tab unnecessary.

This is certainly not the only means of achieving what you want, but it seems to me to be the easiest.

Thanks that works, but only seems to work when I change the tab names to have no spaces? Is it possible to do it so it works with spaces in the tab names?

The issue with doing it your way, is that I have to change the tab names. Is there a way, to do an if statement, where you say if cell A1 in tab1:tab8 = "something in summary sheet" make this the active tab?

 

[FDibbins]

try this addition...
=INDEX(INDIRECT("'"&A1&"!'$B$8:$I$8"),MATCH(1,INDIRECT("'"&A1&"!'$B$3:$I$3")))

 

[propertymodeller]

I tried the above

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #006107}span.s2 {color: #0057d6}span.s3 {color: #ab30d6}</style>=INDEX(INDIRECT(""&A2&"!$B$9:$I$9"),MATCH(1,INDIRECT(""&A2&"!$B$4:$I$4"),0))

But only seems to work when there are no spaces in the tab names still

why do you have a 'before the !

 

[propertymodeller]

Is there any function in excel where you can say

If cell a1 between tab asset1:asset50 = "criteria" make this tab active then apply an index match?

If the above can be done it avoids having to change tab names to enable date to be pulled out.

Issue I have is I need to pull out data from multiple different tabs.

I have IRR on an annual basis, irr on an quarterly basis and monthly.

If I use the index(indrect(match approach, I can only do this by creating a coloumn which matches the tab name