Can a formula look for data in a number of worksheets?

[Guest]

Hello people

I have a problem. I have a formula in a huge range of cells on worksheet 1 which relate to information on worksheet 2. Worksheet 2 has been renamed to coincide with the weekending date.

Obviously, as the weeks go on, I need to copy worksheet 2 and rename that sheet according to the date. Unfortunately, Sheet 1 will only look for the required data in worksheet 2 when I need it to look for the same data but in all of the newly copied worksheets.

Is this possible? The formula I have so is:

=IF(SUMPRODUCT(--('WE 30.07.04'!$A$6:$A$82=4),--('WE 30.07.04'!$C$6:$C$82=$A13)),"X","")

 

[hgrove]

Lee Kelly wrote...

I have a problem. I have a formula in a huge range of cells on
worksheet 1 which relate to information on worksheet 2.
Worksheet 2 has been renamed to coincide with the
weekending date.

Obviously, as the weeks go on, I need to copy worksheet 2 and
rename that sheet according to the date. Unfortunately, Sheet
1 will only look for the required data in worksheet 2 when I
need it to look for the same data but in all of the newly copied
worksheets.

Is this possible? The formula I have so is:

=IF(SUMPRODUCT(--('WE 30.07.04'!$A$6:$A$82=4),
--('WE 30.07.04'!$C$6:$C$82=$A13)),"X","")

If I understand you correctly, you could give a cell in the workshee
containing this formula the defined name WE_Date, then enter the wee
ending date of interest in it. Then you could change your formula to

=IF(SUMPRODUCT(--(INDIRECT(TEXT(WE_Date,"'\W\E dd.mm.yy'!")
&"A6:A82")=4),--(INDIRECT(TEXT(WE_Date,"'\W\E dd.mm.yy'!")
&"C6:C82")=$A13)),"X","")

Now, if you also need to use these conditional counts elsewhere, it'
be a bad idea to calculate them again, so an alternative would b
changing these formulas to just the conditional counts,

=SUMPRODUCT(--(INDIRECT(TEXT(WE_Date,"'\W\E dd.mm.yy'!")
&"A6:A82")=4),--(INDIRECT(TEXT(WE_Date,"'\W\E dd.mm.yy'!")
&"C6:C82")=$A13))

and giving them the custom number format "X";"";"". Then cells wit
nonzero counts would display "X" but would hold the conditional coun
for use in other calculations

 

[Guest]

Thanks hgrove but I dont think I've explained myself very well.

Sheet 1 is a ledger which contains plot numbers of houses on a new building site. I look at this ledger to monitor the sites progress.

I have tablulated the data.
From Cell B1 - P1 are the plot numbers and Cell A2 - A21 are Item Descriptions.
In each of the cells in the table the formula mentioned is entered.

=IF(SUMPRODUCT(--('WE 30.07.04'!$A$6:$A$82=4),

As you can see, the formula will only pick up the required data from the worksheet 'WE 30.07.04'.

I would like to be able to create a new worksheet for each week ending date and therefore would like to know if it is possible for the formula in the Cells of my table to search for the required data but in X amount of worksheets?

 

[Harlan Grove]

As you can see, the formula will only pick up the required data
from the worksheet 'WE 30.07.04'.

I would like to be able to create a new worksheet for each week
ending date and therefore would like to know if it is possible
for the formula in the Cells of my table to search for the
required data but in X amount of worksheets?

....

Do you mean sum and count across multiple worksheets or refer to the
worksheet representing the most recent date?

 

[Guest]

Hi Harlan

Once a date has passed, I should not need to alter the worksheet
representing that date so therefore I would like the formula to look for the
worksheet representing the most recent date if this is possible.

Many thanks for your assistance.
Lee