Formula operating on multiple worksheets

[tvkrys]

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

 

[wongm003]

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

 

[tvkrys]

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]

That would create a circular reference and you'd get an error...

 

[tvkrys]

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...
Krys