Need to look across multiple sheets, to match the name and then return the information within a cell

HollywoodStu

Hi there,

I'll try and make this as simple as possible!

I have a finance sheet (Picture 1) where I want to be able to create a formula to pull in the cell value, by matching the name of a School (in this instance), by searching across 9 different sheets, and return the value within a cell.

So, for example, School A submits a pre-delivery invoice or delivery invoice.

I want a) the Submitted Wider Project Costs to search across all 9 Region sheets (Picture 2) and return whatever value is in the 'Wider Project Costs' cell that match the name of the School

and b) to return the cell value in the 'Pre-Delivery Cost (Year 1)' if a pre delivery invoice is submitted or 'Delivery Cost (Year 1) if a Delivery invoice is submitted.

I've set up three columns in the finance sheet, all titled 'Submitted...' and realise I'll need one for each - 1 for Wider costs to just return the value whether its pre or post delivery invoice, 2 to return Pre-Delivery cell figure for that School if Pre deliver invoice is submitted in finance sheet and 3 to return the Delivery cell figure for the School is a Delivery invoice is submitted in the finance sheet

I hope someone can help me - i can't wrap my head around it!!

Thank you in advance! Happy to provide further information if it helps!

Paul Newcome

@HollywoodStu The first thing I notice is that there is a comma missing before the last zero in the first formula - the one with all of the IFERROR statements.

J Tech

Hi @HollywoodStu

For example, to search across 9 different sheets for the Wider Project Costs and return the matching value in the 'Submitted Wider Project Costs' column, you could use the following formula:

=INDEX({Sheet1!Wider Project Costs;Sheet2!Wider Project Costs;Sheet3!Wider Project Costs;Sheet4!Wider Project Costs;Sheet5!Wider Project Costs;Sheet6!Wider Project Costs;Sheet7!Wider Project Costs;Sheet8!Wider Project Costs;Sheet9!Wider Project Costs}, MATCH(School, {Sheet1!School;Sheet2!School;Sheet3!School;Sheet4!School;Sheet5!School;Sheet6!School;Sheet7!School;Sheet8!School;Sheet9!School}, 0))

Andrée Starå

Hi @J Tech

I hope you're well and safe!

Your solution is in an Excel format and not structured correctly for Smartsheet, so it won't work, unfortunately.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

HollywoodStu

Thanks for your comment @J Tech , i'll look into this.

@Andrée Starå - @J TechI know you said this set up wouldn't work in Smartsheet - do you know if there's something similar that could be adapted for this?

HollywoodStu

@J Tech , @Andrée Starå ,

I've got something down, which is:

=INDEX({East Midlands Region Range 1}; {East Region Range 1}; {North East Region Range 1}; {North West Region Range 1}; {South East Region Range 1}; {South West Region Range 1}; {West Midlands Region Range 1}; {Yorkshire Region Range 1}, MATCH([School Name]:[School Name]@row, {East Midlands Region Range 2}; {East Region Range 2}; {North East Region Range 2}; {North West Region Range 2}; {South East Region Range 2}; {South West Region Range 2}; {West Midlands Region Range 2}; {Yorkshire Region Range 2}, 0))

This obviously doesn't work.

1 The Index Region Range is for the 'Wider Project Costs' column within each of the 9 sheets

2 The Match Region Range is for the 'School Name' column within each of the 9 sheets

But i'm not sure how to get it to search for, example, 'ABC School' that's the school that's in the Finance sheet - would it be @cell at somepoint in the formula?

Thank you for taking time to read/respond!

HollywoodStu

@Paul Newcome - I'm hoping you might be able to help me with this. I've seen you comment on other posts similar to this one. I'm trying to return the value in a 'Wider Projects Costs' column, by matching the School name in a Finance sheet, against 8 of the Region sheets we have. I've created another formula:

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX({East Midlands Region Range 1}, MATCH([School Name]@row, {East Midlands Region Range 2}, 0)), INDEX({East Region Range 1}, MATCH([School Name]@row, {East Region Range 2},0))),INDEX({North East Region Range 1},MATCH([School Name]@row,{North East Region Range 2},0))),INDEX({North West Region Range 1}, MATCH([School Name]@row, {North West Region Range 2},0))),INDEX({South East Region Range 1}, MATCH([School Name]@row, {South East Region Range 2},0))),INDEX({South West Region Range 1}, MATCH([School Name]@row, {South West Region Range 2},0))),INDEX({West Midlands Region Range 1}, MATCH([School Name]@row, {West Midlands Region Range 2},0))),INDEX({Yorkshire Region Range 1}, MATCH([School Name]@row, {Yorkshire Region Range 2}0)))

Neither of them work, but I feel i'm getting closer (perhaps hubris on my part!)

Can you help at all? I'm at a loss!

HollywoodStu

@Paul Newcome - I'm hoping you might be able to help me with this. I've seen you comment on other posts similar to this one, where i'm trying to return the value in the 'Wider Projects Costs' column, by matching the School name in the Finance sheet, against 8 of the Region sheets we have.

I've now created two formulas that looks like:

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX({East Midlands Region Range 1}, MATCH([School Name]@row, {East Midlands Region Range 2}, 0)), INDEX({East Region Range 1}, MATCH([School Name]@row, {East Region Range 2},0))),INDEX({North East Region Range 1},MATCH([School Name]@row,{North East Region Range 2},0))),INDEX({North West Region Range 1}, MATCH([School Name]@row, {North West Region Range 2},0))),INDEX({South East Region Range 1}, MATCH([School Name]@row, {South East Region Range 2},0))),INDEX({South West Region Range 1}, MATCH([School Name]@row, {South West Region Range 2},0))),INDEX({West Midlands Region Range 1}, MATCH([School Name]@row, {West Midlands Region Range 2},0))),INDEX({Yorkshire Region Range 1}, MATCH([School Name]@row, {Yorkshire Region Range 2}0)))

and also

=INDEX({East Midlands Region Range 1}, MATCH([School Name]@row, {East Midlands Region Range 2},0)), INDEX({East Region Range 1}, MATCH([School Name]@row, {East Region Range 2},0)),INDEX({North East Region Range 1},MATCH([School Name]@row,{North East Region Range 2},0)),INDEX({North West Region Range 1}, MATCH([School Name]@row, {North West Region Range 2},0)),INDEX({South East Region Range 1}, MATCH([School Name]@row, {South East Region Range 2},0)),INDEX({South West Region Range 1}, MATCH([School Name]@row, {South West Region Range 2},0)),INDEX({West Midlands Region Range 1}, MATCH([School Name]@row, {West Midlands Region Range 2},0)),INDEX({Yorkshire Region Range 1}, MATCH([School Name]@row, {Yorkshire Region Range 2},0))

Neither of them work, but I feel i'm getting closer (perhaps hubris on my part!)

Can you help at all? I'm at a loss!

Paul Newcome

@HollywoodStu The first thing I notice is that there is a comma missing before the last zero in the first formula - the one with all of the IFERROR statements.

HollywoodStu

Thanks Paul,

I managed to crack this, this weekend - I think it might have been a couple of drinks and whilst watching Terminator 2 that it worked out, so perhaps Skynet had something to do with it!

Got it with the below!

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX({London Sport Range 1}, MATCH([School Name]@row, {London Sport Range 2}, 0)), INDEX({East Midlands Region Range 1}, MATCH([School Name]@row, {East Midlands Region Range 2}, 0))), INDEX({East Region Range 1}, MATCH([School Name]@row, {East Region Range 2}, 0))), INDEX({North East Region Range 5}, MATCH([School Name]@row, {North East Region Range 2}, 0))), INDEX({North West Region Range 1}, MATCH([School Name]@row, {North West Region Range 2}, 0))), INDEX({South East Region Range 1}, MATCH([School Name]@row, {South East Region Range 2}, 0))), INDEX({South West Region Range 1}, MATCH([School Name]@row, {South West Region Range 2}, 0))), INDEX({West Midlands Region Range 1}, MATCH([School Name]@row, {West Midlands Region Range 2}, 0))), INDEX({Yorkshire Region Range 1}, MATCH([School Name]@row, {Yorkshire Region Range 2}, 0)))