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" <(E-Mail Removed)> wrote in message
news:A2A1CF5D-1E49-47D0-9EF0-(E-Mail Removed)...
> 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
|
Similar Threads
