Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CHOOSE and External References
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CHOOSE and External References
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CHOOSE and External References
Thanks much Bernie - this helps a lot.
-- Scott S "Bernie Deitrick" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External References | Excel Programming | |||
Using External VBA References for 64 and 32 bit XP | Excel Programming | |||
Named formulas in CHOOSE need to be Relative references when paste | Excel Worksheet Functions | |||
Modify Paste Special to choose absolute or relative references | Excel Programming | |||
External References | Excel Discussion (Misc queries) |