send value of cell in ACTIVE SHEET to a different sheet

I have 2 worksheets that are pretty similar in design and layout, but different enough to be separate and used for different scenario comparisons. I also have a third worksheet (called calculations) that preforms various calculations that are then used in the original 2 sheets. There is only 1 value that is "sent" (by "sent' I mean cell B5 in the calculations sheet refers to a cell in one of the other sheets to grab that value in it) to the calculations sheet and then it preforms those various other calculations based on that one value. What I need is for the value of cell B5 in the calculations sheet to refer to whatever "active" sheet I am on. This sounds more confusing that what it really is. An example might explain it more...

Sheet 1 and Sheet 2 are similar in layout. In the Calculations sheet, cell B5 is currently referred to cell C6 in sheet 1 (='sheet1'!C6). My problem is, if I am VIEWING Sheet 2 I need the reference in the calculations sheet to be 'Sheet2'!C6, but if VIEWING sheet 1 have the reference be 'Sheet1!C6. The point of this is to "send" the right value for further calculations based on the active viewed sheet.

Is this possible and how do I do it

Thanks

You could have a "worksheet activate event" for each of Sheet1 and Sheet2 so that when you select them the value on the calculations sheet changes.

BSB

The attached will change the value in C5 on the Calculation sheet depending on which sheet (1 or 2) you select.

To see how it's done, right click the tab at the bottom of Sheet1 or Sheet2 and select "View Code".

Hope that helps.

BSB

Thanks but there are two problems... First the sample you provided doesn't seem do what I need. As a matter of fact it doesn't seem to do anything at all. I don't know if there was just a simple error in the design or you misunderstood what I need.
Second, I would like if at all possible, NOT use VBA. I will be viewing this also with my iPhone and the Numbers app and that does not support workbooks using VBA.

If there were an imaginary function that competed this task it would look like this:
in cell B5 of the Calculations sheet would be: =if(the name of the current worksheet in view"="Sheet1,Then:'sheet1'!C6, Else:'sheet2'!C6

Obviously this would not work, but is there a function that can calculate what that literally says?

I can assure you it does do something. The value on the Calculations sheet changes to either Sheet1 or Sheet2, depending on which one you were viewing just before looking at the calculations sheet. It was merely a demonstration of how it could be done, you would need to adapt it to your own workbook.
But as you now mention you're looking for a non-VBA approach that is a moot point.

For what it's worth, I'm not sure there is a non-VBA solution to this.

BSB

Never mind... Thanks, it works. I had this whole description on how it wasn't working, and about to send it to you, but then last minute I realized that I never clicked to enable the macros upon open of the workbook.