Indirect Function?

[Loki2400]

I have some formulas that I want to change with the name of the worksheet. I have a cell called PxW that has the name of the sheet that is the dashboard in it; "10x4" for instance. I make pairs of worksheets; a dashboard sheet that would be called "10x4" and a data sheet that has a matching name "10x4 WOs". My issue is each week I make a new sheet for the week and need the following formula to change so I do not have to go in to all 30 formulas and update the "10x3" to "10x4" and so on. I have been dabbling with the indirect function and I believe I am close but must have some sytax wrong. Here is an example of the stagnant formula:

=SUMPRODUCT(('10x3 WOs'!$E$6:$E$300="TCPKG")*('10x3 WOs'!$I$6:$I$300<>"PM-PM/MSS"))

The formula is on the worksheet entitled '10x3' and it is getting data from the '10x3 WOs' sheet. I tried inserting the following in for the highlighted portion but it did not work:

INDIRECT("PxW")&" "&"WOs"

I have this formula on my worksheet and it is returning '10x4 WOs' which is the proper name for the sheet, but I can not make it work as part of the whole formula above.

I am not dead set on using the indirect function, it was just the direction I chose. I just need the '10x3' part of my formula to index with the sheet so each week I do not have to change it manually.

Any help would be great! I really appreciate it! Thanks!

 

[Sal Paradise]

Try this:

INDIRECT("'"&INDIRECT("PxW")&" "&"WOs'!$E$6:$E$300")

and

INDIRECT("'"&INDIRECT("PxW")&" "&"WOs'!$I$6:$I$300")

Alternatively, you could just add a "Week" cell at the top, and insert whatever week you want it to be (for instance, 10x4).

Say that is Cell A1. You can then simplify as follows:

INDIRECT("'"&A1&" WOs'!$E$6:$E$300")

 

[Marcelo Branco]

Maybe

=SUMPRODUCT((INDIRECT("'"&PxW&" WOs'!E6:E300")="TCPKG")*(INDIRECT("'"&PxW&" WOs'!I6:I300")<>"PM-PM/MSS"))

M.

 

[Loki2400]

Thank you very much!

 

[Marcelo Branco]

Thank you very much!

You are welcome and thanks for the feedback.
(not sure if you used my suggestion or Sal Paradise's but the important is that you solved out your problem)

M.