Hi all -

I store a fomula on Formulas! to be copied to Data! at run-time.
The formula is stored in $B1 and ref's $B1 so I get a circular ref
error.

What can I do?
Thanks
-marc

bump

Any help out there please?

Thanks
-goss

Not certain what the problem is. A function that refers to itself is
certainly a circular reference. Is the function supposed to refer to
itself? In cases where a circular reference is necessary, you have to
enable iteration (Tools - Options - Calculation - check the
iteration box).


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=460233

Hi Shorty -

No I store the formula on a tab named formulas until at runtime the
formula is copied to a tab named data.

The formula is stored in cell B1 on Formula!
It is a lookup formula with syntax =vlookup($B1,mytbl,3,false)

So the circular ref error.

Thanks
-goss




MrShorty wrote:
Not certain what the problem is. A function that refers to itself is
certainly a circular reference. Is the function supposed to refer to
itself? In cases where a circular reference is necessary, you have to
enable iteration (Tools - Options - Calculation - check the
iteration box).


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=460233

So, as I understand it: The formula is stored in "formulas" but doesn'
need to do anything there. When copied to "data," it will be copied t
a cell other than B1, so it doesn't create a circular referenc
there??

If the formula isn't needed to do anything in "formulas," I woul
probably put an apostrophe at the front ('=...) so Excel will treat th
cell as text rather than as a formula. Then remove the apostrophe whe
you copy it to "data.

--
MrShort
-----------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...fo&userid=2218
View this thread: http://www.excelforum.com/showthread.php?threadid=46023