| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with indirect
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
problem with indirect
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
problem with indirect
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
problem with indirect
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
problem with indirect
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
problem with indirect
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
problem with indirect
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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
problem with indirect
You're welcome!
-- Biff Microsoft Excel MVP "Sam" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem with using INDIRECT with SUMPRODUCT and ROW() | Excel Worksheet Functions | |||
| Indirect Formula Problem | Excel Worksheet Functions | |||
| problem using the INDIRECT function | Excel Discussion (Misc queries) | |||
| Indirect problem | Excel Discussion (Misc queries) | |||
| INDIRECT Problem | Excel Discussion (Misc queries) | |||
Linear Mode
