Some Kind of Dynamic Formula

[chriscav]

Sorry if my terminology isn't right or misleading. I'm trying to save some time (in the long run), by making some kind of dynamic formula. I've tried a number of things with no success at present.
Basically my formula as it stands, goes off to a second sheet called BCWS and returns a value in a column based on matching the row value. This all works well.
=IFERROR(INDEX(INDIRECT("'BCWS'!NQ:NQ"),MATCH(B:B,INDIRECT("'BCWS'!A:A"),0)),"0").

Im trying to get the formula to change the BCWS'!NQ:NQ" part, so that when I next run this particular report, it will know that it should no longer look for NQ:NQ but the next/required column along.

CBS	ActivityID	Activity Name	BEP - Control Account	Original Duration	Start	Finish	31-Mar-19	07-Apr-19	14-Apr-19	21-Apr-19	28-Apr-19
DE.DE.60.05	DE.DE.60.05System605-ActiveEffluentCollection&Transfer	2182	01-Apr-15 A	15-Feb-24		831	473	400	376
DE.DE.60.10	DE.DE.60.10System610-LowActiveEffluentCollection&Transfer	2181	01-Apr-15 A	15-Feb-24	4	84	12	12	15
DE.DE.90.00	DE.DE.90.00System900-CommonItemsfor900Systems	1262	01-Apr-15 A	19-May-20					8
DE.DE.90.01	DE.DE.90.01System901-CE&IforCommonItems	1267	01-Apr-15 A	27-May-20
DE.DE.90.08	DE.DE.90.08System908-CompressedAir	1123	01-Apr-15 A	18-Oct-19
DE.DE.90.10	DE.DE.90.10System910-HPSteam	314	01-Apr-15 A	29-Mar-19 A
DE.DE.90.17	DE.DE.90.17System917-Condensate	314	01-Apr-15 A	29-Mar-19 A
DE.DE.90.18	DE.DE.90.18System918-CooledCondensate	314	01-Apr-15 A	29-Mar-19 A
DE.DE.90.20	DE.DE.90.20System920-Demineralisedwater	314	01-Apr-15 A	29-Mar-19 A
DE.DE.90.25	DE.DE.90.25System925-DomesticWater	1171	01-Apr-15 A	28-Nov-19					67
DE.DE.90.30	DE.DE.90.30System930-GroutWashing(R1)Water	314	01-Apr-15 A	29-Mar-19 A
DE.DE.90.40	DE.DE.90.40System940-DryRisers	314	01-Apr-15 A	29-Mar-19 A
DE.DE.90.65	DE.DE.90.65System965-Nitrogen	1161	01-Apr-15 A	11-Dec-19	6	6	5	5	6
DE.DE.90.66	DE.DE.90.66System966-CarbonDioxide	314	01-Apr-15 A	29-Mar-19 A

<colgroup><col><col><col><col><col><col><col><col span="5"></colgroup><tbody>
</tbody>

Now the columns on the BCWS sheet have heading with the date and I'm particularly interested in weeks 3 and 4 in any given period / month. NQ column is 7th April 2019. That's week 3. Week 4 is 14th April 2019. Then in Period / Month 2, weeks 3 and 4 are 19th May and 26th May.


I have another sheet called Validation which has the Periods / Months numbered 1 to 12 and the dates.

				Week 3	Week 4
Period 1	April	31-Mar	07-Apr	14-Apr	21-Apr	28-Apr
Period 2	May	05-May	12-May	19-May	26-May
Period 3	June	02-Jun	09-Jun	16-Jun	23-Jun	30-Jun
Period 4	July	07-Jul	14-Jul	21-Jul	28-Jul
Period 5	August	04-Aug	11-Aug	18-Aug	25-Aug
Period 6	September	01-Sep	08-Sep	15-Sep	22-Sep	29-Sep
Period 7	October	06-Oct	13-Oct	20-Oct	27-Oct
Period 8	November	03-Nov	10-Nov	17-Nov	24-Nov
Period 9	December	01-Dec	08-Dec	15-Dec	22-Dec	29-Dec
Period 10	January	05-Jan	12-Jan	19-Jan	26-Jan
Period 11	February	02-Feb	09-Feb	16-Feb	23-Feb
Period 12	March	01-Mar	08-Mar	15-Mar	22-Mar	29-Mar
				Week 3	Week 4
			Period 1	14/04/2019	21/04/2019

<colgroup><col span="4"><col span="2"><col></colgroup><tbody>
</tbody>

Is it possible to do what I'm looking to do or should I just do a find and replace the "NQ" manually. I guess in essence, I'm trying to negate doing the manual replace and automate it.

Sorry for the long winded post, any help or suggestions will be most gratefully received.

Thanks

 

[BlueAure]

Hi chriscav,

Assuming you are not looking for an explanation of relative references vs absolute ones, you may have to use VBA to change the value of a cell and increment it each time the report is run. There is now way (as far as I know) that a formula can change the actual value of a cell. Hence, an inability to keep track of how many times the report has been run.