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?

 

[inactiveuserps07]

@propertymodeller, I'm sure there's a way to do it with VBA the way you want, but it seems redundant.

You an achieve the same thing by just entering exactly the tab name (spaces and all) in cell A1 of your summary sheet with this variation on the formula I provided above (assuming your original formula works and does what you wanted it to do in the first place):

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

Notice that there is a single quote between double quotes in places. You could make it easier to read this way:

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

Remember: put the name of the target sheet/tab in cell A1 of your summary sheet, not​ whatever is in cell A1 of those individual sheets (which is irrelevant using the above method).

 

[FDibbins]

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

oops had a ' in the wrong place...
=INDEX(INDIRECT("'"&A1&"'!$B$8:$I$8"),MATCH(1,INDIRECT("'"&A1&"'!$B$3:$I$3")))

The ' tells excel there may be a space in the sheet name. Try this...
1. create a new sheet and leave it called sheet10 (change it to that if it is not that)
2. in sheet1, (or any sheet, really), enter =Sheet10!A1 (or just use the mouse to do the reference
3. look at the formula, it shows =Sheet10!A1 with no '
4. Now change sheet10 to sheet 10 (add a space)
5. Go back to the formula and look at it. It now says ='Sheet 10'!A1 instead - note the 2 added '

 

[propertymodeller]

Thanks guys very helpful.

@propertymodeller, I'm sure there's a way to do it with VBA the way you want, but it seems redundant.

You an achieve the same thing by just entering exactly the tab name (spaces and all) in cell A1 of your summary sheet with this variation on the formula I provided above (assuming your original formula works and does what you wanted it to do in the first place):

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

Notice that there is a single quote between double quotes in places. You could make it easier to read this way:

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

Remember: put the name of the target sheet/tab in cell A1 of your summary sheet, not​ whatever is in cell A1 of those individual sheets (which is irrelevant using the above method).

The only issue with this is, each asset has annual cashflow, quarterly cash flow and monthly cash flows. Cell A1 has the unique name for each asset but this is repeated for annual, quarterly, monthly.

The above works well, if I only want to reference a set of tabs within one category, for example IRR Annual tabs for each asset. However, If I then want to pull data from the monthly tabs I will need to create a new coloumn in the summary sheet called IRR Quarterly if I want to reference a separate category of tabs within the spreadsheet. If there was a way I could just say: if cell in summary coloumn = cell A1 in quarterly IRR tab 1: IRR tab quarterly 50, then Index(Match, it would solve my problem as I wouldn't need to rename the tabs but could just select a group of tabs like I do when I 3d sum a group of tabs and index(match the tab within that group that meets the criteria.

Reason I want to do this is I want to avoid naming the tabs as I am rolling the tabs up form individual asset managers and I dont think they will be diligent in naming the tabs in line with how I need it for the model to pick everything up

This would avoid me having to setup a new coloumn in the summary tab for each set of tabs I would like to pull data from