complex formula help, plz help me

[caroline.vanbommel]

hi
i have the following formula

=IF(ISNA(HLOOKUP($B4,'[Monthly master file.xls]Selling 06 01'!$B$2:$BO$300,(MATCH($A$2,'[Monthly master file.xls]Selling 06 01'!$B$2:$B$300,FALSE)),FALSE)),"0",HLOOKUP($B4,'[Monthly master file.xls]Selling 06 01'!$B$2:$BO$300,(MATCH($A$2,'[Monthly master file.xls]Selling 06 01'!$B$2:$B$300,FALSE)),FALSE))

I want to click and drag it into 12 other cells and i want
'[Monthly master file.xls]Selling 06 01'
to change from cell to cell
eg A1 : '[Monthly master file.xls]Selling ACT 06 01'
B2: '[Monthly master file.xls]Selling ACT 06 02'
and so on.

is this possible??
thanks

 

[caroline.vanbommel]

THANK you so much!
It works ive been struggeling with this all day!

 

[Domenic]

Here's a variation of what has already been offered...

Assuming that HLOOKUP returns either a numerical value or #N/A, try...

Insert > Name > Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click Ok

Then try...

C4, copied across:

=LOOKUP(BigNum,CHOOSE({1,2},0,HLOOKUP($B4,INDIRECT("'[Monthly master file.xls]Selling 06 "&TEXT(COLUMNS($C$4:$C4),"00")&"'!$B$2:$BO$300"),(MATCH($A$2,INDIRECT("'[Monthly master file.xls]Selling 06 "&TEXT(COLUMNS($C$4:$C4),"00")&"'!$B$2:$B$300"),FALSE)),FALSE)))

Hope this helps!

 

[Domenic]

Ooops! Change...

COLUMNS($C$4:$C4)

to

COLUMNS($C$4:C$4)

(Andrew, thanks for pointing this out!)