Offset & Indirect

[Rcketfan]

I was reading a thread concerning the OFFSET & INDIRECT functions. I need it to reference a another workbook. I wrote in the formula like the one on the thread and instead of refering to the worksheet in the current workbook, I referenced the other worksheet and workbook. I am getting a REF# back what am I doing wrong or am I able to reference a different workbook. An example of the formula is below. I need to find a way to reference the other workbook and worksheet.

=IF(OFFSET(INDIRECT("Sheet1!A1"),ROW()-1,COLUMN()-1)=0,"",OFFSET(INDIRECT("Sheet1!A1"),ROW()-1,COLUMN()-1))

[VBA Noob]

Hi,

I can't see the other workbook name

e.g

=[Book1]Sheet1!$A$1

Also indirect can only be used if the other workbook is open.

http://www.ozgrid.com/News/IndirectE...hooseArray.htm

unless you use the addin from this site

http://xcell05.free.fr/english/moref...direct.ext.htm

VBA Noob

[Rcketfan]

Here is what I type for the formula

=IF(OFFSET(INDIRECT('[JPAE - JobPower to Excel.xls]JPAE-JobPower to Excel'!A200),ROW()-1,COLUMN()-1)=0,"",OFFSET(INDIRECT('[JPAE - JobPower to Excel.xls]JPAE-JobPower to Excel'!A200),ROW()-1,COLUMN()-1))

[VBA Noob]

So the book name is same as the sheet name ??

Instead of typing try selecting the other workbook

VBA Noob

[Rcketfan]

That's what I did. In where the ("Sheet1!A1") was. I highlighted and then switched over to the other book and selected the cell I wanted to refer to. Does it have to always reference "A1" cell. I noticed in a spreadsheet that I downloaded that every cell referenced that particular cell. If you notice my references "A200".

[VBA Noob]

Think you are trying to run before you can walk. Take a look at excel help re offset's to understand what cell you think you need to reference


VBA Noob