Nested Index/Match

Khari Shiver

Hello,

I am currently trying to pull data into a primary sheet from several ancillary sheets. I thought about taking the data from the ancillary sheets, 8 of them, and putting them into one sheet but there are simply too may opportunities to mess the data up. I also hoped I could create a report that pulled in the necessary data from the sheets and run a single index/match from that report. I quickly found out you cannot use a report in an index/match formula.

So, I'm now at a place where I think I'm going to have to use a nested IF statement with index/match as the true condition. I'd like to know if anyone else has done this OR if they have any suggestions that will prevent me from having 7 nested IF statements.

Thank you

Paul McGuinness

Hi @Khari Shiver

Unfortunately it looks like you will need to use the nested formula to perform your calculations, it is possible and can be tricky dependent on the various criteria your calculating.

Couple of pointers learnt the hard way:

Shorten the name of your cross sheet references, if the sheet name is for example 'calculation sheet 1 range 1' then shorten to something like CS1R1 or whatever works for you as the number of characters in the formula are limited, i don't think you should hit the limit across 7 sheets but it does make it tidier for you to review.

When writing the formula pay attention to your brackets and make sure to close them out correctly

When writing long formulas i find using notepad or even a blank word doc helpful to lay them out and make sure the structure is right.

If the formula isn't working but you are sure its correct try saving and refreshing the sheet, I've experienced it a couple of times when a broken formula started working upon a save and refresh.

and if all else fails try the community if you get stuck.

Hope that helps

Thanks and good luck!

Paul

Paul McGuinness

Hi @Khari Shiver

Unfortunately it looks like you will need to use the nested formula to perform your calculations, it is possible and can be tricky dependent on the various criteria your calculating.

Couple of pointers learnt the hard way:

Shorten the name of your cross sheet references, if the sheet name is for example 'calculation sheet 1 range 1' then shorten to something like CS1R1 or whatever works for you as the number of characters in the formula are limited, i don't think you should hit the limit across 7 sheets but it does make it tidier for you to review.

When writing the formula pay attention to your brackets and make sure to close them out correctly

When writing long formulas i find using notepad or even a blank word doc helpful to lay them out and make sure the structure is right.

If the formula isn't working but you are sure its correct try saving and refreshing the sheet, I've experienced it a couple of times when a broken formula started working upon a save and refresh.

and if all else fails try the community if you get stuck.

Hope that helps

Thanks and good luck!

Paul

Khari Shiver

Thank you for your input!

Khari