Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automating TAB names in calculations
I have an Excel 2003 workbook that I have set-up as a resource spreadsheet
currently with one resource. I am now trying to make it easier to add new resources. Each resource will have a task sheet and a holiday planner and I am trying to automate calculations using TAB names. Is there a way I can store a TAB name and then use this stored item so that when I add a new resource I can point calculations to the new tab name. I have a couple of examples of existing calculations below. If I add a new resource I will have to repoint all the calculations. =SUM(AbsencesJL!F:F) =VLOOKUP('Jeff Langdon'!H5,AbsencesJL!$E7:$I56,5) -- Thanks for your help Jeff L |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automating TAB names in calculations
Maybe you can use a dedicated cell to hold the worksheet names (say A1).
Then you can use a formula like: =sum("'"&$a$1&"'!f:f") or if you had two cells that held both worksheet names: =VLOOKUP("'"&$b$1&"'!h5","'"&$a$1&"'!E7:I56",5) or if you're looking for an exact match in your =vlookup() formula, then you want to use False or 0 as that 4th parm. =VLOOKUP("'"&$b$1&"'!h5","'"&$a$1&"'!E7:I56",5,0) Jeff L wrote: I have an Excel 2003 workbook that I have set-up as a resource spreadsheet currently with one resource. I am now trying to make it easier to add new resources. Each resource will have a task sheet and a holiday planner and I am trying to automate calculations using TAB names. Is there a way I can store a TAB name and then use this stored item so that when I add a new resource I can point calculations to the new tab name. I have a couple of examples of existing calculations below. If I add a new resource I will have to repoint all the calculations. =SUM(AbsencesJL!F:F) =VLOOKUP('Jeff Langdon'!H5,AbsencesJL!$E7:$I56,5) -- Thanks for your help Jeff L -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automating TAB names in calculations
With your sheet name in Cell A1 try
=SUM(INDIRECT("'" & A1&"'!F:F")) For getting the sheet name you can use the below formula..in cell A1 =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99) If this post helps click Yes --------------- Jacob Skaria "Jeff L" wrote: I have an Excel 2003 workbook that I have set-up as a resource spreadsheet currently with one resource. I am now trying to make it easier to add new resources. Each resource will have a task sheet and a holiday planner and I am trying to automate calculations using TAB names. Is there a way I can store a TAB name and then use this stored item so that when I add a new resource I can point calculations to the new tab name. I have a couple of examples of existing calculations below. If I add a new resource I will have to repoint all the calculations. =SUM(AbsencesJL!F:F) =VLOOKUP('Jeff Langdon'!H5,AbsencesJL!$E7:$I56,5) -- Thanks for your help Jeff L |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automating TAB names in calculations
VLOOKUP would go as
=VLOOKUP(INDIRECT("'" & A1 & "'!H5"),INDIRECT("'" & A2 & "'!$E7:$I56"),5) with the below names in A1 and A2 Jeff Langdon AbsencesJL If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: With your sheet name in Cell A1 try =SUM(INDIRECT("'" & A1&"'!F:F")) For getting the sheet name you can use the below formula..in cell A1 =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99) If this post helps click Yes --------------- Jacob Skaria "Jeff L" wrote: I have an Excel 2003 workbook that I have set-up as a resource spreadsheet currently with one resource. I am now trying to make it easier to add new resources. Each resource will have a task sheet and a holiday planner and I am trying to automate calculations using TAB names. Is there a way I can store a TAB name and then use this stored item so that when I add a new resource I can point calculations to the new tab name. I have a couple of examples of existing calculations below. If I add a new resource I will have to repoint all the calculations. =SUM(AbsencesJL!F:F) =VLOOKUP('Jeff Langdon'!H5,AbsencesJL!$E7:$I56,5) -- Thanks for your help Jeff L |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automating Worksheet Names | Excel Worksheet Functions | |||
Semi-automating the generation of multiple cell range names | Excel Discussion (Misc queries) | |||
Automating Tab names | Excel Worksheet Functions | |||
Visual Basics - Automating Specific Sheet Names | Excel Discussion (Misc queries) | |||
Automating using VBA | Excel Worksheet Functions |