Formula operating on multiple worksheets

tvkrys

Board Regular
Joined
Jan 23, 2006
Messages
135
Hopefully, this is an easy one... Is it 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? I have a cludgey way to do it now that is a SendKeys solution with several steps (which requires cut, paste and replace functions to get the results in the correct cell!!!) As I said, it works but I'd like an single, elegant formula if there is one.

Assume the formula goes into A1 on the first sheet. It subtracts the contents of B1 on the worksheet that is to the immediate right of the first sheet from B1 on the original sheet.

TIA, Krys
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

wongm003

Board Regular
Joined
Aug 8, 2005
Messages
237
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
 

tvkrys

Board Regular
Joined
Jan 23, 2006
Messages
135
See?!?!? I knew there was a simple, elegant way to do that!!!

How would the code/formula change if, in the right-hand worksheet B2, you put a formula that says it should equal A1 of the left-hand worksheet (again, without regard to names of worksheets)?

Thank you so much for the help!
Krys
 

wongm003

Board Regular
Joined
Aug 8, 2005
Messages
237
That would create a circular reference and you'd get an error...
 

tvkrys

Board Regular
Joined
Jan 23, 2006
Messages
135
Sorry, guess I didn't make myself clear... This would be an entirely different situation - not in tandem with what you've already given me... I'm just trying to learn more about how I could apply this type of code/formula to other situations. If I can see what changes for a different situation, it usually helps me figure that out...
:p Krys
 

Forum statistics

Threads
1,136,272
Messages
5,674,753
Members
419,525
Latest member
helensesc

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