Some Kind of Dynamic Formula

chriscav

New Member
Joined
May 22, 2019
Messages
1
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.

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

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.
[TABLE="width: 478"]
<colgroup><col span="4"><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Week 3[/TD]
[TD]Week 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 1[/TD]
[TD]April[/TD]
[TD="align: right"]31-Mar[/TD]
[TD="align: right"]07-Apr[/TD]
[TD="align: right"]14-Apr[/TD]
[TD="align: right"]21-Apr[/TD]
[TD="align: right"]28-Apr[/TD]
[/TR]
[TR]
[TD]Period 2[/TD]
[TD]May[/TD]
[TD="align: right"]05-May[/TD]
[TD="align: right"]12-May[/TD]
[TD="align: right"]19-May[/TD]
[TD="align: right"]26-May[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 3[/TD]
[TD]June[/TD]
[TD="align: right"]02-Jun[/TD]
[TD="align: right"]09-Jun[/TD]
[TD="align: right"]16-Jun[/TD]
[TD="align: right"]23-Jun[/TD]
[TD="align: right"]30-Jun[/TD]
[/TR]
[TR]
[TD]Period 4[/TD]
[TD]July[/TD]
[TD="align: right"]07-Jul[/TD]
[TD="align: right"]14-Jul[/TD]
[TD="align: right"]21-Jul[/TD]
[TD="align: right"]28-Jul[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 5[/TD]
[TD]August[/TD]
[TD="align: right"]04-Aug[/TD]
[TD="align: right"]11-Aug[/TD]
[TD="align: right"]18-Aug[/TD]
[TD="align: right"]25-Aug[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 6[/TD]
[TD]September[/TD]
[TD="align: right"]01-Sep[/TD]
[TD="align: right"]08-Sep[/TD]
[TD="align: right"]15-Sep[/TD]
[TD="align: right"]22-Sep[/TD]
[TD="align: right"]29-Sep[/TD]
[/TR]
[TR]
[TD]Period 7[/TD]
[TD]October[/TD]
[TD="align: right"]06-Oct[/TD]
[TD="align: right"]13-Oct[/TD]
[TD="align: right"]20-Oct[/TD]
[TD="align: right"]27-Oct[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 8[/TD]
[TD]November[/TD]
[TD="align: right"]03-Nov[/TD]
[TD="align: right"]10-Nov[/TD]
[TD="align: right"]17-Nov[/TD]
[TD="align: right"]24-Nov[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 9[/TD]
[TD]December[/TD]
[TD="align: right"]01-Dec[/TD]
[TD="align: right"]08-Dec[/TD]
[TD="align: right"]15-Dec[/TD]
[TD="align: right"]22-Dec[/TD]
[TD="align: right"]29-Dec[/TD]
[/TR]
[TR]
[TD]Period 10[/TD]
[TD]January[/TD]
[TD="align: right"]05-Jan[/TD]
[TD="align: right"]12-Jan[/TD]
[TD="align: right"]19-Jan[/TD]
[TD="align: right"]26-Jan[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 11[/TD]
[TD]February[/TD]
[TD="align: right"]02-Feb[/TD]
[TD="align: right"]09-Feb[/TD]
[TD="align: right"]16-Feb[/TD]
[TD="align: right"]23-Feb[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period 12[/TD]
[TD]March[/TD]
[TD="align: right"]01-Mar[/TD]
[TD="align: right"]08-Mar[/TD]
[TD="align: right"]15-Mar[/TD]
[TD="align: right"]22-Mar[/TD]
[TD="align: right"]29-Mar[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Week 3[/TD]
[TD]Week 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Period 1[/TD]
[TD="align: right"]14/04/2019[/TD]
[TD="align: right"]21/04/2019[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



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 :)
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top