problem with indirect

[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. Valko]

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

 

[Sam]

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. Valko]

Try this (all on one line):

IF(ISBLANK(INDIRECT("'["&$A$3&" "
&$I$1&".xls]other'!"&ADDRESS(ROWS(A$1:A5),
COLUMNS($A11)))),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?

 

[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

Try this (all on one line):

IF(ISBLANK(INDIRECT("'["&$A$3&" "
&$I$1&".xls]other'!"&ADDRESS(ROWS(A$1:A5),
COLUMNS($A11)))),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. 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($A11))

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 A11 references. Leave them just as they
are.

--
Biff
Microsoft Excel MVP

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($A11)))),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?

 

[Sam]

got ya

thank you very much

sam

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($A11))

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 A11 references. Leave them just as they
are.

--
Biff
Microsoft Excel MVP

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($A11)))),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. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

got ya

thank you very much

sam

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($A11))

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 A11 references. Leave them just as
they
are.

--
Biff
Microsoft Excel MVP

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($A11)))),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?