got ya

thank you very much

sam

"T. Valko" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
hi bliff

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

sam

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Sam" wrote in message
...
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?