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

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

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

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