hi guys , this is my problem:

i got two worksheets and sheet 1 has got datas and sheet two is exactly
as sheet 1 and the datas are reference to sheet 1.

so in sheet 1 A1, the value is 5

in sheet 2 A1 , the formula used is =sheet1!A1

the formula applies for the column A in sheet 2 .

the problem is : if i delete any rows( shift rows up) in sheet 1 ,
sheet 2 will

return #REF!


Is there any way of preventing that?


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=487511

Hi!

Try this:

=INDIRECT("Sheet1!A1")

That will always refer to Sheet1 A1 no matter if you insert rows or delete
rows.

Biff

"cjjoo" wrote in
message ...

hi guys , this is my problem:

i got two worksheets and sheet 1 has got datas and sheet two is exactly
as sheet 1 and the datas are reference to sheet 1.

so in sheet 1 A1, the value is 5

in sheet 2 A1 , the formula used is =sheet1!A1

the formula applies for the column A in sheet 2 .

the problem is : if i delete any rows( shift rows up) in sheet 1 ,
sheet 2 will

return #REF!


Is there any way of preventing that?


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=487511

If i use the indirect formula, i cant just click and drag for the
different rows i am refering to . Is there a solution to this?


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=487511

Hi!

Try this:

=INDIRECT("Sheet1!A"&ROWS($1:1))

Biff

"cjjoo" wrote in
message ...

If i use the indirect formula, i cant just click and drag for the
different rows i am refering to . Is there a solution to this?


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=487511

what i am doing is that i want to picked out some datas in sheet 1 that
fulfil a certain criteria(e.g those that has "apples")


but in sheet two, with the indirect function, i cant delete the blank
rows . Is there a way out for this?

the scenario:

sheet 1:
apples
oranges
apples
orange

sheet 2
apples

apples



in sheet two , i want to delete the empty rows but i realised that it
is not possible. Pls advise


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=487511

You could use a formula like this in sheet 2

IF(ISERROR(SMALL(IF(Sheet1!$A1:$A20="apples",ROW($ A1:$A20),""),ROW($A1:$A20)
)),"",
INDEX(Sheet1!$A$1:$A$20,SMALL(IF(Sheet1!$A1:$A20=" apples",ROW($A1:$A20),""),
ROW($A1:$A20))))

To enter it, select the number of rows you think you might need for items to
be copied, then enter the formula in the formula bar, and commit it with
Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"cjjoo" wrote in
message ...

what i am doing is that i want to picked out some datas in sheet 1 that
fulfil a certain criteria(e.g those that has "apples")


but in sheet two, with the indirect function, i cant delete the blank
rows . Is there a way out for this?

the scenario:

sheet 1:
apples
oranges
apples
orange

sheet 2
apples

apples



in sheet two , i want to delete the empty rows but i realised that it
is not possible. Pls advise


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=487511