CHOOSE and External References

[ScottS]

Hello all,

My client makes extensive use of long CHOOSE formulas that references cells
in another large workbook. They are constantly using these CHOOSE formulas
for buliding "reports" in dozens of workbooks. I am concerned that any
changes to the structure of the large source workbook would require a
mega-editing effort.

e.g.: CHOOSE($B$1,'[Data File.xls]Actual'!F23,'[Data
File.xls]Actual'!G23,'[Data File.xls]Actual'!H23,'[Data
File.xls]Actual'!I23,'[Data File.xls]Actual'!J23,'[Data
File.xls]Actual'!K23,'[Data File.xls]Actual'!L23,'[Data
File.xls]Actual'!M23,'[Data File.xls]Actual'!N23,'[Data
File.xls]Actual'!O23,'[Data File.xls]Actual'!P23,'[Data File.xls]Actual'!Q23)

I am recommending setting up the CHOOSE functionality using VBA functions
they can use where ever they need. This way a structural change could be
taken care of in one place.

Would it be best to use the CHOOSE in my function or is there a better way?
Is there a special syntax for referencing another workbook from within a VBA
function?
(I'm coming from Access here and am just getting up to speed on VBA in Excel).

Thanks!

Scott

 

[Bernie Deitrick]

Scott,

Their formula

=CHOOSE($B$1,'[Data File.xls]Actual'!F23,'[Data
File.xls]Actual'!G23,'[Data File.xls]Actual'!H23,'[Data
File.xls]Actual'!I23,'[Data File.xls]Actual'!J23,'[Data
File.xls]Actual'!K23,'[Data File.xls]Actual'!L23,'[Data
File.xls]Actual'!M23,'[Data File.xls]Actual'!N23,'[Data
File.xls]Actual'!O23,'[Data File.xls]Actual'!P23,'[Data File.xls]Actual'!Q23)

would better be

=INDEX('[Data File.xls]Actual'!$F$23:$Q$23,1,$B$1)

But note that the range to select (the F23:Q23) could also be made dynamic. Suppose you had a table
on the Actual sheet with column headings in row 1 and row labels in column A. Then you could use a
formula like

=INDEX('[Data File.xls]Actual'!$1:$65536,MATCH("Label for Row 23",'[Data
File.xls]Actual'!$A:$A,FALSE),MATCH("Col Heading",'[Data File.xls]Actual'!$1:$1,FALSE))

This would extract specific data by labels rather than being tied to an exact row or column,
allowing for the insertion and deletion of rows and columns of data (as long as column A and Row 1
stayed put). The "Label for Row 23" and "Col Heading" could also be cell references.

That would make it much more robust.

HTH,
Bernie
MS Excel MVP

 

[ScottS]

Thanks much Bernie - this helps a lot.
--
Scott S

Scott,

Their formula

=CHOOSE($B$1,'[Data File.xls]Actual'!F23,'[Data
File.xls]Actual'!G23,'[Data File.xls]Actual'!H23,'[Data
File.xls]Actual'!I23,'[Data File.xls]Actual'!J23,'[Data
File.xls]Actual'!K23,'[Data File.xls]Actual'!L23,'[Data
File.xls]Actual'!M23,'[Data File.xls]Actual'!N23,'[Data
File.xls]Actual'!O23,'[Data File.xls]Actual'!P23,'[Data File.xls]Actual'!Q23)

would better be

=INDEX('[Data File.xls]Actual'!$F$23:$Q$23,1,$B$1)

But note that the range to select (the F23:Q23) could also be made dynamic. Suppose you had a table
on the Actual sheet with column headings in row 1 and row labels in column A. Then you could use a
formula like

=INDEX('[Data File.xls]Actual'!$1:$65536,MATCH("Label for Row 23",'[Data
File.xls]Actual'!$A:$A,FALSE),MATCH("Col Heading",'[Data File.xls]Actual'!$1:$1,FALSE))

This would extract specific data by labels rather than being tied to an exact row or column,
allowing for the insertion and deletion of rows and columns of data (as long as column A and Row 1
stayed put). The "Label for Row 23" and "Col Heading" could also be cell references.

That would make it much more robust.

HTH,
Bernie
MS Excel MVP

Hello all,

My client makes extensive use of long CHOOSE formulas that references cells
in another large workbook. They are constantly using these CHOOSE formulas
for buliding "reports" in dozens of workbooks. I am concerned that any
changes to the structure of the large source workbook would require a
mega-editing effort.

e.g.: CHOOSE($B$1,'[Data File.xls]Actual'!F23,'[Data
File.xls]Actual'!G23,'[Data File.xls]Actual'!H23,'[Data
File.xls]Actual'!I23,'[Data File.xls]Actual'!J23,'[Data
File.xls]Actual'!K23,'[Data File.xls]Actual'!L23,'[Data
File.xls]Actual'!M23,'[Data File.xls]Actual'!N23,'[Data
File.xls]Actual'!O23,'[Data File.xls]Actual'!P23,'[Data File.xls]Actual'!Q23)

I am recommending setting up the CHOOSE functionality using VBA functions
they can use where ever they need. This way a structural change could be
taken care of in one place.

Would it be best to use the CHOOSE in my function or is there a better way?
Is there a special syntax for referencing another workbook from within a VBA
function?
(I'm coming from Access here and am just getting up to speed on VBA in Excel).

Thanks!

Scott