Replace link with sum across consecutive worksheets that have relative references

[Vaslo]

Hello again everyone,

I'm trying to do the following -
1) Create a summary tab that is identical to a group of tabs preceding it.
2) Replace a link with a "sum across tabs" function, i.e =sum(CP:2996_LE!C12)
3) The key is that the cell should be replaced with sum across functions, but it MUST have a reference to the same cell, so i.e. the cell below the one above says (CP:2996_LE!C13), etc.

I'm lost on how to do this, my basic code looks like this, but I don't know how to do a replace with "relative reference" if you will:

Please Login or Register  to view this content.

Thank you!

[Leith Ross]

Hello Vaslo ,

Can you be more specific about what you want to do?

'but how do I make the C12 change to reflect the cell it's in??

A Cell can not reference itself in a formula.

[Vaslo]

Leith,

I want to replace a string with a sum across worksheets. The key is, I want the replace to look across the worksheet and find everywhere that string is and replace it. The key however is that it basically deletes that string and puts in the sum across worksheets but its not an absolute replacement. It finds the string deletes it and then sums THAT particular cell across 7 worksheets. Then it moves to the next cell with the string, deletes the string, sums THAT cell across worksheets, etc.

Does that make more sense?

[Leith Ross]

Hello Vaslo ,

I am not sure I understand this correctly. Using your example of cell C12, can you walk through the steps?

Please Login or Register  to view this content.

[Vaslo]

Thanks Leith been working on this all darn morning...

Ok here is what I want to do (would post sheet but its full of confidential info):
1) I have a workbook. It has 8 tabs. At the end is a summary tab. That summary tab should correspond to the sum of the 7 tabs before it.
2) Currently there are, say 100 cells that have values that need to be summed across. They are all over the place. The ONLY thing they share is an identical link that references an outside work book.
3) The sum will be the same across all cells EXCEPT that it will sum that cell only across the 7 tabs. So for example, you will have \Consolidated and Parsed by Template\Initial Test\[Copy of Manual_Schedules_BU_Strat Plan_final TEST.xlsx]'Brand Sales!C7 and I want to sum up the C7 tabs across those seven in the summary. Then the code will find the next instance of the '\Consolidated and Parsed by Template\Initial Test\[Copy of Manual_Schedules_BU_Strat Plan_final TEST.xlsx]' link (probably in D7) then sum the D7s across.

I can't do this manually as it sums like a few hundred cells, and I'm going to create a bunch more workbooks like it. The problem as you can see is that my find replace is static and would force me to put in a static cell rather than the cell it corresponds to. Does that make more sense?

In short:
-Find a string in a particular cell
-replace that string with a sum across worksheets to sum the previous 7 tabs
-make sure that the cell sums the ActiveCell.Value across those tabs, if you will.

Hope that helps, thought this would be so easy, but nothing in VBA seems to be for the inexperienced .

Greg

[Vaslo]

I tried the following code as well, but can't get it to change one formula to another:

Please Login or Register  to view this content.

When I replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

I get a replacement that obviously isn't a formula, and it puts the remaining string (which is the cell reference) on the outside of the bracket of course. I know this probably so simple, what am I missing?