Next Page Diff and Equals Previous

[tvkrys]

Last October, wongm003 helped me solve a problem I was having. I asked if it is possible to create a formula that results in the difference between a cell on one worksheet and a cell on the worksheet to its immediate right - regardless of the names of the worksheets?

His solution:

If you want to create your own function...

Create a module in the workbook and add this code. In A1 type in "=next_page_diff(B1)". The function gives you the value of the cell that is put in the parameter minus the cell in the page to the right (next index). If the values in the cells are not numeric the cell with the formula will be blank.

Public Function next_page_diff(inCell) As Variant 
On Error Resume Next 

    next_page_diff = "" 
    next_page_diff = Sheets(inCell.Worksheet.Index).Range(inCell.Address).Value - Sheets((inCell.Worksheet.Index) + 1).Range(inCell.Address).Value 

End Function

This worked great - UNTIL I upgraded from Win2000 to XP. Now all I get in every new cell is the “ $NAME? ” error in the cell. I’m still using same Excel version and I verified that the module holding the code does still exist in the file. Oddly enough, WSs that were created back in the Win2000 days reflect the correct value when viewed in XP.

1. Any ideas on what’s happen and how I can correct this?

2. I also have need for a similar setup that will simply show the value of a specific cell (not the difference between cells) on the PREVIOUS worksheet – regardless of the names of the worksheet. A new worksheet is created everyday to the left and a cell in this new worksheet needs to display the total from the WS to the right. How would formula and function code change? (D1 targets H37 on worksheet to the left.)

TIA, Krys

 

[kmillen]

tvkrys,

Seriously reexamine why the physical proximity of one sheet to another is the criterion for subtracting two values. By imposing this restriction in your sheet design, you are ignoring more powerful features in Excel and its ability to interact between sheets. The proof of this poor design is the very problem you're having. I do not say this maliciously because sometimes, redesign is required and even prudent.

Good Luck

 

[tvkrys]

kmillen

While I appreciate your response, it doesn't really help me very much. I know very little about programming in VBA and I'm not certain why this needs to be re-examined as the two sheets will always be next to to each other.

I have a macro that creates a new sheet from a template and places it in the "sheet 3" position. This naturally moves the previous "sheet 3" to the "sheet 4" position and the positions cycle up with every new WS. They are not named "sheet x' as the macro also renames the new sheet with the current date. This means I can't use sheet name as it is a moving target and I can't use "sheet x" as that is a moving target as well. Seems to me, proximity is the only constant.

What would you suggest as an alternative?