Formula that knows its display location

[shai-hulud]

Hello,

Is it possible to create a formula that will display different results
depending on which cell links to it?

For instance, cell A1 contains a formula.
Cell B1 says '=A1'
Cell C1 says '=A1'
The formula in cell A1 changes the results depending on whether it is
being displayed in cell B1 or C1, so all three cells have different
results.

Is that possible?

Very Respectfully,
Neil

 

[shai-hulud]

Edit--

By the way, the example that I am giving is an oversimplification of
what I am trying to do, but if it is possible will allow me to figure
out my actual situation (much more difficult to explain).

Neil

 

[shai-hulud]

I figured out how to do it if the cells are on different sheets. That
works well enough for now . I am just checking which sheet is the
ActiveSheet in the formula and updating the display that way. If there
are any other suggestions, I am all ears .

 

[shai-hulud]

Does anyone know how to force an update for a formula on an inactive
sheet?

Neil

 

[NickHK]

I don't understand your logic:

B1 says '=A1'
C1 says '=A1'

But B1<>C1 ??

NickHK

 

[shai-hulud]

Ok, the specific context of how this would be useful:
I have a table with information about a product. The product uses a
formula to determine its price. That formula uses statistics based on
location. On another sheet, I have all of the information from one
location, and on a third sheet, I have all of the information for a
second location. Now, I have a drop-down list where the user can select
the product. Then, using VLOOKUPs, I populate all of the product
information into the location sheet. Most of the products load
correctly. A few have special circumstances. The solution that I have
come up with is that on the product data sheet, I would like to use a
formula that will update based on the current active sheet. The problem
is that, once I select one of the location sheets, the product data
sheet does not update with the new active sheet. Is there some way to
force the cells on the product sheet to update when either of my two
location sheets become activated? This would allow the formula in one
cell to display different information depending on which location is
active.

 

[NickHK]

Not sure I follow all the requirements, but each worksheet has a _Deactivate
event.
You can code changes and/or .Calculate from there.

NickHK

 

[shai-hulud]

Pretty much, I am trying to force an update on the Products sheet when
either of the two Location sheets become activated. Unfortunately, when
a sheet is not active, its formula's do not update, even if data
changes that would change the formula. I created the following
function:

Function ActiveSheetName()
ActiveSheetName = ActiveSheet.Name
End Function

Then, on the products table, for the products that I was having
problems with, I have the following format for the formulae

=IF(ActiveSheetName()="Location1",Location1Data,IF(ActiveSheetName()="Location2",Location2Data,""))

So, when I select the Location1 sheet, the Location1Data should show,
and when I select the Location2 sheet, the Location2Data should show.
Unfortunately, because the Products sheet is not active, the formula
does not reevaluate. So, when Location1 or Location2 becomes the active
sheet, I want to force a refresh on the data on the Products sheet.

Does this clear up the problem at all?

 

[NickHK]

Yes, because changing sheets does not trigger a recalculation.
That why I said you could probably use the Worksheet_Activate/Deactivate
events to call a recalc.
Worksheets("Products").Calculate

Although I feel there would be better of achieving your goal.

NickHK

 

[shai-hulud]

I got it to work. Here is what I did for my test. I have a module with
the following formulae:
Function ActiveSheetName()
ActiveSheetName = ActiveSheet.Name
End Function

Function ReEvaluate(rngEval As Range)
ReEvaluate = Evaluate(rngEval.Formula)
End Function

Sheet1!A1 contains the formula:
=IF(ActiveSheetName()="Sheet2","Hello",IF(ActiveSheetName()="Sheet3","There",""))
Sheet2!A1 contains the formula: =ReEvaluate(Sheet1!A1)
Sheet3!A1 contains the formula: =ReEvaluate(Sheet1!A1)

So now, Sheet1!A1 displays ""
Sheet2!A1 displays "Hello"
Sheet3!A1 displays "There"

Thanks everyone who tried to help. This solution will work.