indirect?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cjjoo - 23 Nov 2005 04:59 GMT

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

Reply to this Message

Biff - 23 Nov 2005 05:17 GMT

Hi!

Try this:

=INDIRECT("Sheet1!A1")

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

Biff

> hi guys , this is my problem:
>
[quoted text clipped - 13 lines]
>
> Is there any way of preventing that?

Reply to this Message

cjjoo - 23 Nov 2005 06:01 GMT

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?

Signature

cjjoo

Reply to this Message

Biff - 23 Nov 2005 06:23 GMT

Hi!

Try this:

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

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?

Reply to this Message

cjjoo - 23 Nov 2005 09:02 GMT

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

Signature

cjjoo

Reply to this Message

Bob Phillips - 23 Nov 2005 11:11 GMT

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

Signature

HTH

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

> 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")
[quoted text clipped - 17 lines]
> in sheet two , i want to delete the empty rows but i realised that it
> is not possible. Pls advise

Reply to this Message