subtraction from a previous sheet vba

[ysherriff]

to all, good evening.

i created multiple workbook that has 12 tabs or sheets titled "week 1", "week 2" ,etc all the way to "week 3". all the tabs have similar contents and setup.

There is a column on each tab titled "variance" and starting in week 2, there is an additional column titled "last week variance". how can i automate the process when i generate my workbooks and worksheets where it can take the last week variance information from the cell and populate it into the current week.

I have attached a final sample worksheet as an example that i manually did. Please look at Week 2 and Week 3 in the column titled" LAst Week Variance". I need to automate this section in my code.I have also included the generator code that created these workbooks. The code is titled mod_generate_workbook

Thank you much for your help.

 

[Luke M]

Hello ysherriff.

Formula in your sheet needs to be:
=INDIRECT("'Week "&MID($E$3,6,3)-1&"'!N"&ROW())

To populate this in further sheets, first, there's a small error in workbook generator. Says to make "Week 2 " <-- extra space. Need to remove that extra space.

Then, to get formula in future workbooks, looks like you need to modify your Template worksheet. Should just need to paste in the formula. I suspsect that in the Templace, formual will evaluate to an error, as there is no 'previous' week yet.

 

[ysherriff]

Thanks Luke. I will try as you say.

 

[ysherriff]

Luke, If you don't mind. can you explain the mechanics of this formula? I appreciate the formula but it also helps me to learn the mechanics as well.

I appreciate it sir.

 

[Luke M]

Sure. Formula copied for reference:
=INDIRECT("'Week "&MID($E$3,6,3)-1&"'!N"&ROW())

The INDIRECT function lets you create/build a text string, and the function then tries to evaluate it as if it was an reference. So, if you passed it the text "A1", it would translate that into cell A1, and go look there. Or, we could pass:
"A"&1
which XL would concatenate into:
"A1"
and again, would know to go look at cell A1.

The two inner functions are MID and ROW. The ROW function is simply returning the row number of the cell containing formula. So, if it's in E7, it evaluate to 7.

The MID function goes to cell E3, and then says to start at the 6th character (2nd argument), and take 3 characteres (3rd argument). E3 contains the sheet name, "Week XX". Note that if the last argument is longer than actual word, XL know to just take everything till the end of the word. So, if E3 says "Week 10", then the function goes to 6th character, the 1, and tries to take next 3. There's only 2 characters, so it returns "10". If E3 = "Week 2", then MID takes the "2".
Now, we want to know previous week. So, whatever number we find, we subtract 1.

Let's assume that current formula is on sheet "Week 4", and is in cell O9. The MID function pulled out our 4, and reduced it to 3. The ROW function returned a 9.
Altogether then, inside the INDIRECT function, we build this string
"'Week " & 3 & "'!N" & 9
which mashed together is:
"'Week 3'!N9"
Hey, that looks like a proper XL reference!
XL now knows to look at the previous week's sheet, and goes to cell N9, and gets our value. Awesome!

When we copy our formula down, the ROW function will change it's output to 10, and our INDIRECT then knows to go look at:
"'Week 3'!N10"

 

[ysherriff]

I might be doing something wrong. when i tried it. it doesn't work. here is a sample file. Look at week 2 and week 3 tab. i understood your formula explanation and i apprecaite it but i must be doing something wrong.

 

[Luke M]

It's actually a small typo, I mentioned above.

To populate this in further sheets, first, there's a small error in workbook generator. Says to make "Week 2 " <-- extra space. Need to remove that extra space.

Change the one worksheet's name from "Week 2 " to be "Week 2" (no extra space), and things will work again. The space is causing a problem because formula on sheet 3 is looking for "Week 2", which has not space.