INDIRECT Function Change

[dohsan]

Hi,


I've noticed some strange behaviour in the INDIRECT function between older versions of excel and 2010.


I have a workbook that performs a set of looks up against another workbook.


The below worked fine:


=MATCH(C8,INDIRECT("'[&$A$1&]RP1'!$C:C"),0)


C8 is a product code

A1 is the name of the other workbook (including file extension)


This would return a row number that then would feed into some of other lookups


We recently upgraded to Excel 2010 so the workbook being "looked up" is now a .xlsm file, whereas the "lookup" workbook is still in .xls format. This formula was now returning a #REF error.


After scratching my head for a bit I managed to get it working with the following


=MATCH(C8,INDIRECT("'[&$A$1&]RP1'!$C1:C1000"),0)


Is it a new feature that you have to specify the actual range, rather than the whole column?


Now I've got that working ok, I'm getting a similar issue with a HLOOKUP/INDIRECT Formula


=CONCATENATE(HLOOKUP($CC$6,INDIRECT("'[&$A$1&]RP1'!$7:$1000"),$B$8-6,0),HLOOKUP($CD$6,INDIRECT("'[&$A$1&]RP1'!$7:$1000"),$B$8-6,0))


CC6 is a header name

CD6 is a header name

Row 7 is the row on the sheet that has all the headers

a1 again is the name of the sheet thats being looked into

b8 is the row number that was returned with the original forumla up top


Again the INDIRECT returns a #REF - anyone have any ideas?

 

[dohsan]

I've managed to answer my own question!


=CONCATENATE(HLOOKUP($CC$6,INDIRECT("'[&$A$1&]RP1'!$b7:$id1000"),$B$8-6,0),HLOOKUP($CD$6,INDIRECT("'[&$A$1&]RP1'!$b7:$id1000"),$B$8-6,0))


appears to now work with the lookup - again having the specify the whole range as it were

 

[NARAYANK991]

Hi ,


It's good that you could get things to work ; as you understood , the reason is the increase in limits from older versions of Excel to Excel 2007 and later.


To see this , just open a .xls file , and a .xlsx or .xlsm file ; with both files open , in the .xls file , enter the formula :


=match(C8,'[Just another file.xlsm]RP1'!$C:$C,0)


You will get an error message :


Invalid Reference. This file version cannot contain formulas that reference cells beyond a worksheet size of 256 columns or 65536 rows.


The formula itself is in a .xls file , whose limits are lower than the limits in the file which is being referenced ( this being a .xlsx or .xlsm file is of version Excel 2007 or later ).


When you give an address reference by changing the above formula to :


=match(C8,'[Just another file.xlsm]RP1'!$C1:$C1000,0)


there are no error messages.


If you enter this formula in the .xlsx or .xlsm file , referencing the .xls file :


=match(C8,'[Just another file.xls]RP1'!$C:$C,0)


there will not be any error message.


Narayan