problem with indirect

S

Sam

i am trying to use indirect to look up another workbook to see whether or not
the cell is blank and hence the ISBLANK. This formula works but i can not
fill it across or down as this cell (D5) remains constant.

IF(ISBLANK(INDIRECT("'["&$A$3&" "&$I$1&".xls]other'!D5")),sheet1!D4,"")

any ideas?
 
T

T. Valko

How far do you want to copy it across? Will the last reference go beyond
column Z: other'!Z5 ?
 
S

Sam

hi bliff

no. the range is D5:U13 in the other workbook.

sam

T. Valko said:
How far do you want to copy it across? Will the last reference go beyond
column Z: other'!Z5 ?

--
Biff
Microsoft Excel MVP


Sam said:
i am trying to use indirect to look up another workbook to see whether or
not
the cell is blank and hence the ISBLANK. This formula works but i can not
fill it across or down as this cell (D5) remains constant.

IF(ISBLANK(INDIRECT("'["&$A$3&" "&$I$1&".xls]other'!D5")),sheet1!D4,"")

any ideas?
 
T

T. Valko

Try this (all on one line):

IF(ISBLANK(INDIRECT("'["&$A$3&" "
&$I$1&".xls]other'!"&ADDRESS(ROWS(A$1:A5),
COLUMNS($A1:D1)))),sheet1!D4,"")

--
Biff
Microsoft Excel MVP


Sam said:
hi bliff

no. the range is D5:U13 in the other workbook.

sam

T. Valko said:
How far do you want to copy it across? Will the last reference go beyond
column Z: other'!Z5 ?

--
Biff
Microsoft Excel MVP


Sam said:
i am trying to use indirect to look up another workbook to see whether
or
not
the cell is blank and hence the ISBLANK. This formula works but i can
not
fill it across or down as this cell (D5) remains constant.

IF(ISBLANK(INDIRECT("'["&$A$3&" "&$I$1&".xls]other'!D5")),sheet1!D4,"")

any ideas?
 
S

Sam

hi again

I tried your formula but had trouble implementing it into my sheet. I have
come up with an alternative that is half way there. It's a bit messy.

=IF(ISBLANK(INDIRECT("'["&$A$3&"
"&$I$1&".xls]other'!E"&$V4)),Responsibilities!E4,"")

in column "V" i have numbered the rows 5-13 which gives me my range on the
vertical but the column "E" still remains constant in "other'!E"&$V4".

i have tried doing the same as the $V4 by using running the letters in row
48. D$48="D", E$48="E", and so on. If i could replace the "E" in the formula
with E$48 that would do for the fill across. I am sure that it is a simple
case of putting in a & or " but where i do not know. Does this make sense?

sam


T. Valko said:
Try this (all on one line):

IF(ISBLANK(INDIRECT("'["&$A$3&" "
&$I$1&".xls]other'!"&ADDRESS(ROWS(A$1:A5),
COLUMNS($A1:D1)))),sheet1!D4,"")

--
Biff
Microsoft Excel MVP


Sam said:
hi bliff

no. the range is D5:U13 in the other workbook.

sam

T. Valko said:
How far do you want to copy it across? Will the last reference go beyond
column Z: other'!Z5 ?

--
Biff
Microsoft Excel MVP


i am trying to use indirect to look up another workbook to see whether
or
not
the cell is blank and hence the ISBLANK. This formula works but i can
not
fill it across or down as this cell (D5) remains constant.

IF(ISBLANK(INDIRECT("'["&$A$3&" "&$I$1&".xls]other'!D5")),sheet1!D4,"")

any ideas?
 
T

T. Valko

I tried your formula but had trouble implementing it into my sheet.

What kind of trouble? It works just fine for me.

ADDRESS(ROWS(A$1:A5),COLUMNS($A1:D1))

That expression returns the *text* address $D$5. As you copy across, the
column will increment: $E$5, $F$5, $G$5, etc. As you copy down, the row will
increment: $D$5, $D$6, $D$7, $D$8, etc.

Don't "mess" with the A1:A5 or the A1:D1 references. Leave them just as they
are.

--
Biff
Microsoft Excel MVP


Sam said:
hi again

I tried your formula but had trouble implementing it into my sheet. I have
come up with an alternative that is half way there. It's a bit messy.

=IF(ISBLANK(INDIRECT("'["&$A$3&"
"&$I$1&".xls]other'!E"&$V4)),Responsibilities!E4,"")

in column "V" i have numbered the rows 5-13 which gives me my range on the
vertical but the column "E" still remains constant in "other'!E"&$V4".

i have tried doing the same as the $V4 by using running the letters in row
48. D$48="D", E$48="E", and so on. If i could replace the "E" in the
formula
with E$48 that would do for the fill across. I am sure that it is a simple
case of putting in a & or " but where i do not know. Does this make sense?

sam


T. Valko said:
Try this (all on one line):

IF(ISBLANK(INDIRECT("'["&$A$3&" "
&$I$1&".xls]other'!"&ADDRESS(ROWS(A$1:A5),
COLUMNS($A1:D1)))),sheet1!D4,"")

--
Biff
Microsoft Excel MVP


Sam said:
hi bliff

no. the range is D5:U13 in the other workbook.

sam

:

How far do you want to copy it across? Will the last reference go
beyond
column Z: other'!Z5 ?

--
Biff
Microsoft Excel MVP


i am trying to use indirect to look up another workbook to see
whether
or
not
the cell is blank and hence the ISBLANK. This formula works but i
can
not
fill it across or down as this cell (D5) remains constant.

IF(ISBLANK(INDIRECT("'["&$A$3&"
"&$I$1&".xls]other'!D5")),sheet1!D4,"")

any ideas?
 
S

Sam

got ya

thank you very much

sam

T. Valko said:
I tried your formula but had trouble implementing it into my sheet.

What kind of trouble? It works just fine for me.

ADDRESS(ROWS(A$1:A5),COLUMNS($A1:D1))

That expression returns the *text* address $D$5. As you copy across, the
column will increment: $E$5, $F$5, $G$5, etc. As you copy down, the row will
increment: $D$5, $D$6, $D$7, $D$8, etc.

Don't "mess" with the A1:A5 or the A1:D1 references. Leave them just as they
are.

--
Biff
Microsoft Excel MVP


Sam said:
hi again

I tried your formula but had trouble implementing it into my sheet. I have
come up with an alternative that is half way there. It's a bit messy.

=IF(ISBLANK(INDIRECT("'["&$A$3&"
"&$I$1&".xls]other'!E"&$V4)),Responsibilities!E4,"")

in column "V" i have numbered the rows 5-13 which gives me my range on the
vertical but the column "E" still remains constant in "other'!E"&$V4".

i have tried doing the same as the $V4 by using running the letters in row
48. D$48="D", E$48="E", and so on. If i could replace the "E" in the
formula
with E$48 that would do for the fill across. I am sure that it is a simple
case of putting in a & or " but where i do not know. Does this make sense?

sam


T. Valko said:
Try this (all on one line):

IF(ISBLANK(INDIRECT("'["&$A$3&" "
&$I$1&".xls]other'!"&ADDRESS(ROWS(A$1:A5),
COLUMNS($A1:D1)))),sheet1!D4,"")

--
Biff
Microsoft Excel MVP


hi bliff

no. the range is D5:U13 in the other workbook.

sam

:

How far do you want to copy it across? Will the last reference go
beyond
column Z: other'!Z5 ?

--
Biff
Microsoft Excel MVP


i am trying to use indirect to look up another workbook to see
whether
or
not
the cell is blank and hence the ISBLANK. This formula works but i
can
not
fill it across or down as this cell (D5) remains constant.

IF(ISBLANK(INDIRECT("'["&$A$3&"
"&$I$1&".xls]other'!D5")),sheet1!D4,"")

any ideas?
 
T

T. Valko

You're welcome!

--
Biff
Microsoft Excel MVP


Sam said:
got ya

thank you very much

sam

T. Valko said:
I tried your formula but had trouble implementing it into my sheet.

What kind of trouble? It works just fine for me.

ADDRESS(ROWS(A$1:A5),COLUMNS($A1:D1))

That expression returns the *text* address $D$5. As you copy across, the
column will increment: $E$5, $F$5, $G$5, etc. As you copy down, the row
will
increment: $D$5, $D$6, $D$7, $D$8, etc.

Don't "mess" with the A1:A5 or the A1:D1 references. Leave them just as
they
are.

--
Biff
Microsoft Excel MVP


Sam said:
hi again

I tried your formula but had trouble implementing it into my sheet. I
have
come up with an alternative that is half way there. It's a bit messy.

=IF(ISBLANK(INDIRECT("'["&$A$3&"
"&$I$1&".xls]other'!E"&$V4)),Responsibilities!E4,"")

in column "V" i have numbered the rows 5-13 which gives me my range on
the
vertical but the column "E" still remains constant in "other'!E"&$V4".

i have tried doing the same as the $V4 by using running the letters in
row
48. D$48="D", E$48="E", and so on. If i could replace the "E" in the
formula
with E$48 that would do for the fill across. I am sure that it is a
simple
case of putting in a & or " but where i do not know. Does this make
sense?

sam


:

Try this (all on one line):

IF(ISBLANK(INDIRECT("'["&$A$3&" "
&$I$1&".xls]other'!"&ADDRESS(ROWS(A$1:A5),
COLUMNS($A1:D1)))),sheet1!D4,"")

--
Biff
Microsoft Excel MVP


hi bliff

no. the range is D5:U13 in the other workbook.

sam

:

How far do you want to copy it across? Will the last reference go
beyond
column Z: other'!Z5 ?

--
Biff
Microsoft Excel MVP


i am trying to use indirect to look up another workbook to see
whether
or
not
the cell is blank and hence the ISBLANK. This formula works but i
can
not
fill it across or down as this cell (D5) remains constant.

IF(ISBLANK(INDIRECT("'["&$A$3&"
"&$I$1&".xls]other'!D5")),sheet1!D4,"")

any ideas?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top