Multi Sheets

[mcgrath1980]

Hi

I am using the following SUMIF('01012019'!$I$1:$I$1000,"GRID",'01012019'!$F$1:$F$1009)

I need to pick up info from each tab on my sheet dating 01012019 to 31012019.

my question is : Is there an easy way to do this ?? Or would I have to go into every sheet.


Thanks

 

[FormR]

I need to pick up info from each tab on my sheet dating 01012019 to 31012019.

Hi - do you mean you want to perform the same SUMIF() across all the sheets and return the SUM() of all of those in a single formula?

If so, and assuming your sheet names are exactly as you have shown and they represent the ddmmyyyy of a date, you could try:

=SUMPRODUCT(SUMIF(INDIRECT(TEXT(ROW(INDIRECT(DATE(2019,1,1)&":"&DATE(2019,1,31))),"ddmmyyyy")&"!$I$1:$I$1000"),"GRID",INDIRECT(TEXT(ROW(INDIRECT(DATE(2019,1,1)&":"&DATE(2019,1,31))),"ddmmyyyy")&"!$F$1:$F$1009")))

Where the red parts represent the range of dates you are interested in.

FYI, this is often referred to as a "3D SUMIF" - if you wanted to do some googling.

 

[mcgrath1980]

Yes that what I am looking for, I wasn't sure if there was a way of doing this without going through the process of keying in the full text

thanks very much for replying

 

[mcgrath1980]

The date I am referring to is actually sheet 2 to sheet 31 I have just renamed them by date.

And I a looking to pull the information back to sheet 1 which will be my reference sheet or dashboard sheet

 

[FormR]

I have just renamed them by date.

Hi, if they are named as per your example in post#1 then the formula should work - assuming all of those sheets exist.

 

[mcgrath1980]

That doesn't seem to be working for me.

I don't think I have explained my example correctly at first.

I have the following feeding back my sheet 1 for each tab, Could this be put all together 010119 to 310119 ???

SUMIF('020119'!I2:I1000,"GRID",'020119'!F1:F1009)



SUMIF('010119'!I2:I1000,"GRID",'010119'!F1:F1009)

 

[FormR]

That doesn't seem to be working for me.

Hi, could you try to describe "doesn't seem to be working" in a little more detail?

- Do you get an error? if so, what error?
- Do you get an unexpected result? If so, what do you get and how does it differ to what you expect? Try to post a scaled down example that demonstrates the problem?
- Something else? If so - details?

 

[mcgrath1980]

Sorry when I input SUMPRODUCT(SUMIF(INDIRECT(TEXT(ROW(INDIRECT(DATE(2019,1,1)&":"&DATE(2019,1,31))),"ddmmyyyy")&"!$I$1:$I$1000"),"GRID",INDIRECT(TEXT(ROW(INDIRECT(DATE(2019,1,1)&":"&DATE(2019,1,31))),"ddmmyyyy")&"!$F$1:$F$1009")))

it returns #REF

 

[FormR]

it returns #REF

I need to pick up info from each tab on my sheet dating 01012019 to 31012019.

Could this be put all together 010119 to 310119 ???

Hi, please clarify how your sheets are named? Are they named like post # 1 or post # 6? Or something entirely different?

 

[mcgrath1980]

Post 6 now..... Each tab on the sheet is numbered 010119 - 020119 - 030119 - 040119 and so on until 310119