Next Page Diff and Equals Previous

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

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

kmillen

Board Regular
Joined
Jun 8, 2007
Messages
115
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

Board Regular
Joined
Jan 23, 2006
Messages
135
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

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
Top