Formula for results depending on many conditions from few Sheets

[Scavenger102]

Morning everyone,

It would like to create a spreadsheet to show results that matched 3 conditions but from different sheets depending on choice from drop down menu (B1, B2, B3 - Home sheet).

And now I want to show results using drop down menu for site (which would get conditions from sheets accordingly), electricity and cost for year 2014-15 in cells E3 to P4 in sheet HOME if that makes any sense :/

[AliGW]

It would be very helpful if you could provide the following:

1. Any formulae you have tried and failed to get working.
2. Some sample expected outcomes (entered manually) in your sample workbook.

Thanks!

[Scavenger102]

Formula I tried was based on vlookup. I used vookup when I was having when I had one condition form only one sheet. But here I have to bring data from the site I choose (i.e. site 1, site 2 etc) so I wanted to combine this with function Indirect... Many tries and it is not working... The last one I tried was:
E3: {=INDIRECT("'"&B1&"'!",VLOOKUP($B$2&B3&B4,$A$1:$O$100,10,FALSE))}

The results I want to get values from Site 1 in (this example) all values from whole year 2014-15

Screen Shot 2016-12-30 at 09.57.57 (3).png

[AliGW]

Sorry to be a pain, but please would you add those expected outcomes to your sample workbook (manually) and then I'll take a look for you. I don't have time to copy them in myself - sorry.

[AliGW]

Sorry to be a pain, but please would you add those expected outcomes to your sample workbook (manually) and then I'll take a look for you. I don't have time to copy them in myself - sorry.

[Scavenger102]

Screen Shot 2016-12-30 at 10.10.39 (3).png

The idea is to create a chart that would compare utility and cost for a year chosen and for site chosen

So in this example I have chosen from drop down menu - Site 1 & 2014-15 & Electricity - all the values taken from sheet Site 1

Screen Shot 2016-12-30 at 10.46.41 (2).png

But if I chose Site 2 I would like to get all these values from Site 2 sheet

Many thank

[AliGW]

Images are of no use to me. I do NOT have time to copy data from them into your sample file - sorry.

PLEASE add some expected outcomes to your sample file and attach that workbook here. Charts can come later: let's not try to run until we have started walking!

Thanks.

[Scavenger102]

Thanks Ali for all your help

Here is a the file:

[Glenn Kennedy]

You'll need to use an array formula like this one:

=INDEX(INDIRECT("'"&$B$1&"'!$D$1:$O$32"),MATCH(1,(INDIRECT("'"&$B$1&"'!$A$1:$A$32")=Home!$B$2)*(INDIRECT("'"&$B$1&"'!$B$1:$B$32")=Home!$B$3),0),MATCH(Home!E$2,INDIRECT("'"&$B$1&"'!$D$2:$O$2"),0))


Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Tbhis may not fully answer your Q, as I just spotted your new sheet. However, do take a look at htis. I have dummy data for site 1 and 2.

[AliGW]

We can do it without array formulae. Use the following copied across:

In E3: =INDEX(INDIRECT("'"&$B$1&"'!$A$1:$O$500"),MATCH(Home!$B$2,'Site 1'!$A$1:$A$500,0)+LOOKUP($B$3,{"Electricity","Gas","Water"},{2,4,6}),COLUMN()-1)

In E4: =INDEX(INDIRECT("'"&$B$1&"'!$A$1:$O$500"),MATCH(Home!$B$2,'Site 1'!$A$1:$A$500,0)+LOOKUP($B3,{"Electricity","Gas","Water"},{3,5,7}),COLUMN()-1)

In E9: =INDEX(INDIRECT("'"&$B$1&"'!$A$1:$O$500"),MATCH(Home!$B$8,'Site 1'!$A$1:$A$500,0)+LOOKUP($B$3,{"Electricity","Gas","Water"},{2,4,6}),COLUMN()-1)

In E10: =INDEX(INDIRECT("'"&$B$1&"'!$A$1:$O$500"),MATCH(Home!$B$8,'Site 1'!$A$1:$A$500,0)+LOOKUP($B9,{"Electricity","Gas","Water"},{3,5,7}),COLUMN()-1)

[Scavenger102]

That is brilliant, thanks

I will check both

[AliGW]

You're welcome!

Mine depends on the layout of the site sheets being identical and every year to be listed in column A of those sheets.

[Glenn Kennedy]

Tweaked to add on the 2nd row...

[AliGW]

Thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.