I have designed a spreadsheet with multiple worksheets that perform financial
functions. These multiple worksheets pull from one specific line (column 2)
in one of its worksheets (DATA). My idea is that "worksheets 2-5" would pull
from column 2 of DATA as its data is scrubbed and moved elsewhere or deleted.

Problem is I get a #REF! error in worksheets 2-5 anytime data in column 2 of
DATA is deleted. If the data is moved, the cell references in worksheets 2-5
reflect those changes..in other words, the data is static in worksheets that
are supposed to be dynamic.

Umm...newbie thinks he knows why it is doing this but wonders how (I) can
make it so that the multiple worksheets pull data from the values in column 2
of DATA only.

Your help is greatly appreciated!

try the indirect function in your secondary sheets.

=indirect("Sheet1!B"&row())
or
=indirect("Sheet1!B2")




"reloanpro" wrote:

I have designed a spreadsheet with multiple worksheets that perform financial
functions. These multiple worksheets pull from one specific line (column 2)
in one of its worksheets (DATA). My idea is that "worksheets 2-5" would pull
from column 2 of DATA as its data is scrubbed and moved elsewhere or deleted.

Problem is I get a #REF! error in worksheets 2-5 anytime data in column 2 of
DATA is deleted. If the data is moved, the cell references in worksheets 2-5
reflect those changes..in other words, the data is static in worksheets that
are supposed to be dynamic.

Umm...newbie thinks he knows why it is doing this but wonders how (I) can
make it so that the multiple worksheets pull data from the values in column 2
of DATA only.

Your help is greatly appreciated!

Maybe you can use =indirect()

=INDIRECT("'Data'!b2")



reloanpro wrote:

I have designed a spreadsheet with multiple worksheets that perform financial
functions. These multiple worksheets pull from one specific line (column 2)
in one of its worksheets (DATA). My idea is that "worksheets 2-5" would pull
from column 2 of DATA as its data is scrubbed and moved elsewhere or deleted.

Problem is I get a #REF! error in worksheets 2-5 anytime data in column 2 of
DATA is deleted. If the data is moved, the cell references in worksheets 2-5
reflect those changes..in other words, the data is static in worksheets that
are supposed to be dynamic.

Umm...newbie thinks he knows why it is doing this but wonders how (I) can
make it so that the multiple worksheets pull data from the values in column 2
of DATA only.

Your help is greatly appreciated!

--

Dave Peterson

Thanks Dave, bj. Now I get a message that states "the value entered is not
valid. A user has restricted values that can be entered into this cell".

I am the only user on this machine and I do not recall placing any such
restriction.

?? and thanks again...

reloanpro

"bj" wrote:

try the indirect function in your secondary sheets.

=indirect("Sheet1!B"&row())
or
=indirect("Sheet1!B2")




"reloanpro" wrote:

I have designed a spreadsheet with multiple worksheets that perform financial
functions. These multiple worksheets pull from one specific line (column 2)
in one of its worksheets (DATA). My idea is that "worksheets 2-5" would pull
from column 2 of DATA as its data is scrubbed and moved elsewhere or deleted.

Problem is I get a #REF! error in worksheets 2-5 anytime data in column 2 of
DATA is deleted. If the data is moved, the cell references in worksheets 2-5
reflect those changes..in other words, the data is static in worksheets that
are supposed to be dynamic.

Umm...newbie thinks he knows why it is doing this but wonders how (I) can
make it so that the multiple worksheets pull data from the values in column 2
of DATA only.

Your help is greatly appreciated!

Thanks, Dave, bj! It worked like a charm! The invalid value error I wrote
about only happens when from a particular field that I can work around.

I appreciate the help...

"reloanpro" wrote:

Thanks Dave, bj. Now I get a message that states "the value entered is not
valid. A user has restricted values that can be entered into this cell".

I am the only user on this machine and I do not recall placing any such
restriction.

?? and thanks again...

reloanpro

"bj" wrote:

try the indirect function in your secondary sheets.

=indirect("Sheet1!B"&row())
or
=indirect("Sheet1!B2")




"reloanpro" wrote:

I have designed a spreadsheet with multiple worksheets that perform financial
functions. These multiple worksheets pull from one specific line (column 2)
in one of its worksheets (DATA). My idea is that "worksheets 2-5" would pull
from column 2 of DATA as its data is scrubbed and moved elsewhere or deleted.

Problem is I get a #REF! error in worksheets 2-5 anytime data in column 2 of
DATA is deleted. If the data is moved, the cell references in worksheets 2-5
reflect those changes..in other words, the data is static in worksheets that
are supposed to be dynamic.

Umm...newbie thinks he knows why it is doing this but wonders how (I) can
make it so that the multiple worksheets pull data from the values in column 2
of DATA only.

Your help is greatly appreciated!