Hi,
I have VBA code with array formula that looks up some values from series of spreadsheets. Which spreadsheet to use depends on the values in another column.
How do I make the formula the same for the whole range as it will substitute the necessary path into itself?
here is an example:
here is a code for VBA:
Thanks a lot for help!!
I have VBA code with array formula that looks up some values from series of spreadsheets. Which spreadsheet to use depends on the values in another column.
How do I make the formula the same for the whole range as it will substitute the necessary path into itself?
here is an example:
here is a code for VBA:
Code:
Range("I2").Select
Selection.FormulaArray = _
"=INDEX('1_Repl'!$C$2:$C$8,MATCH(1,IF('1_Repl'!$A$2:$A$8=H4,IF('1_Repl'!$C$2:$C$8<>0,1)),0))"
Selection.AutoFill Destination:=Range("I2:I535"), Type:=xlFillDefault
TEST_LOOKUP.xls | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | 1stSpreadsheet | 2ndSpreadsheet | |||||||||||
2 | |||||||||||||
3 | Number | 1 | 2 | 3 | Code | Number | Data2 | ||||||
4 | 123 | 0.2 | 0 | 0.2 | 1 | 123 | 0.22 | Code=1,so1_Repl.xls | |||||
5 | 234 | 0.1 | 0.1 | 0 | 1 | 234 | 0.1 | ||||||
6 | 345 | 0 | 0 | 0 | 2 | 345 | #N/A | hastoreferto2_Repl.xls | |||||
7 | 123 | 0 | 0.22 | 0 | 3 | 456 | #N/A | ||||||
8 | 234 | 0 | 0 | 0.15 | 3 | 567 | #N/A | pathhastobedynamic | |||||
9 | 123 | 0.24 | 0.24 | 0.24 | 3 | 678 | #N/A | ValueofG2+"_Repl.xls" | |||||
10 | 345 | 0.13 | 0 | 0 | |||||||||
11 | |||||||||||||
1_Repl |
Thanks a lot for help!!