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






--
Scott S