Offset Function Help

[Willmonbo]

Here is the scenario that I need help with:

In Column "I" the values entered are always a calculation based on the values entered in column "E" the same row, so I figured that to make it easier I simply can create a name and use the following formula in the name definition:

=Offset(Cell("Address"),,-4,)*.3

but it doesn't work and I can't figure out why, would appreciate someone taking a look at it.

I tried to enter the formula directly into a cell and it doesn't work either, so I am aware that problem is not with it being a defined name but with the formula itself, nevertheless can't figure out what the problem is

 

[barry houdini]

The problem you have, specifically, is that CELL function returns a text string not a cell reference - you could wrap the CELL function in INDIRECT but I'm not sure why you can't just use a formula like this

=E2*0.3

 

[Willmonbo]

Thanks Barry,

it's not always the same calculation and the and it's not a simple *.3, I was just using it to illustrate the scenario.

thanks again I always like your creativity when mentioned either by Mr. Excel in his Podcasts or by ExcellIsFun in his YouTube clips.

 

[Willmonbo]

Barry,

Sorry to bother again with it, the "Indirect" makes it work but it still seems to be quite a volatile formula, I could be working somewhere else on the worksheet and suddenly Column "I" comes up with the "value" error, and then I click into one cell in column "I" with the "value" error and just hit the enter again and the entire column recalculates and is good, what could be the problem?

 

[barry houdini]

Both INDIRECT and OFFSET are volatile functions, best avoided if you can - I'm still not sure why you can't just reference the cell directly, even if you have different calculations.....

 

[Willmonbo]

it's not that I can't just reference the other cell, it's rather a convenience being that it's always one of four possible calculations and they all are at least 6 figures beyond the decimal position.

but since you say that these functions are inherently volatile I may just abandon it completely or perhaps if you have a suggestion of a different way to do it, i'd greatly appreciate it.

thanks much for your time and help.

 

[Willmonbo]

Barry,

I was thinking of an idea how to resolve the volatility problem with the formula I am using, and that is to have a macro constantly run in the background that whenever this formula is entered the macro should go into the cell and evaluate it (as if I would do F9 after entering the formula), I just don't know how to write VBA code, can you help me with that?