indirect?

  • Thread starter Thread starter cjjoo
  • Start date Start date
C

cjjoo

hi guys , this is my problem:

i got two worksheets and sheet 1 has got datas and sheet two is exactl
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
 
Hi!

Try this:

=INDIRECT("Sheet1!A1")

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

Biff
 
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?
 
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
 
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)
 
Back
Top