tvkrys
Board Regular
- Joined
- Jan 23, 2006
- Messages
- 135
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:
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
His solution:
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.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.
Code: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
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