variable worksheet references

All - Having problems with creating an unusual reference.

I have a workbook which contains a template that users will copy and rename as many times as they need to e.g. Analysis Template becomes Analysis 1, Analysis 2, Analysis 3, and so on. I've created a summary page which collects the highest level results from each of these worksheets (one row on the row per worksheet) - but am having problems telling excel which worksheet to go to find the value.

What I need for this summary page is for Excel to recognize that I need 'Analysis ' and ID number depending on the row of the summary & '!Cell reference' - I could go through and do each row manually - but I am distributing this workbook to several different user groups each of which may have any number of different template copies - so it needs to happen automatically based on the number of copies they create.

Any thoughts?

Thanks -
Matt

You just need the maximum value?

If yes, how about this.

You create two "helper" worksheets. One named Start and one named Finish.
(Lock all the cells and protect these two worksheets--just so no one uses them.)

Put all your Analysis worksheets (and no others!) between those two worksheets.

Then to get the highest value from A1 in all those sheets, you could use:

=max(Start:Finish!a1)

The users could even play what-if games. If they drag "Analysis 33" out of the
Start:Finish "sandwich", then that worksheet's values will be ignored.





mattbloodgood wrote:
>
> All - Having problems with creating an unusual reference.
>
> I have a workbook which contains a template that users will copy and
> rename as many times as they need to e.g. Analysis Template becomes
> Analysis 1, Analysis 2, Analysis 3, and so on. I've created a summary
> page which collects the highest level results from each of these
> worksheets (one row on the row per worksheet) - but am having problems
> telling excel which worksheet to go to find the value.
>
> What I need for this summary page is for Excel to recognize that I need
> 'Analysis ' and ID number depending on the row of the summary & '!Cell
> reference' - I could go through and do each row manually - but I am
> distributing this workbook to several different user groups each of
> which may have any number of different template copies - so it needs to
> happen automatically based on the number of copies they create.
>
> Any thoughts?
>
> Thanks -
> Matt
>
> --
> mattbloodgood
> ------------------------------------------------------------------------
> mattbloodgood's Profile: http://www.excelforum.com/member.php...o&userid=24999
> View this thread: http://www.excelforum.com/showthread...hreadid=385267

--

Dave Peterson

Dave - Thanks for the reply - I like the direction you are going but I need to be careful to associate rows on the summary page with the appropriate template copy. For instance, they are going to copy the Template and call it analysis 1. On the summary page, I am extracting several specific cells (e.g. A10, C10, E10, G10)from Analysis 1 and putting them all on the same row. In the next row, I am pulling the same cells from Analysis 2, on the third row, I'm pulling the same cells from Analysis 3 and so on for every template copy they make.

Where I run into a problem is that I have no idea how many copies each team will ultimately make so the forumla needs to be flexible (which you've accomplished with the (start:finish helpers). My summary page is sourced from several other pages in the workbook including one where they describe qualitatively each of the different analysis types and provide an identifier - so is there some way that I can make this conditional on the Summary Row ID equaling the ID (contained in cell B2 for instance) on each Analysis template?

Thanks again -
Matt

Can you use a macro to do the work?

This copies the values from your given addresses:

Option Explicit
Option Base 0
Sub testme01()

Dim wks As Worksheet
Dim SummaryWks As Worksheet
Dim myAddr As Variant
Dim iCtr As Long
Dim oRow As Long

Set SummaryWks = Worksheets("summary")
myAddr = Array("A10", "C10", "E10", "G10")

With SummaryWks
'headers in row 1
.Range("A2:E65536").ClearContents

oRow = 2 'first row after headers
For Each wks In ActiveWorkbook.Worksheets
If LCase(wks.Name) Like "analysis*" Then
.Cells(oRow, "A").Value = wks.Name
For iCtr = LBound(myAddr) To UBound(myAddr)
.Cells(oRow, iCtr + 2).Value = wks.Range(myAddr(iCtr))
Next iCtr
oRow = oRow + 1
End If
Next wks
End With

End Sub


===
If you want the formula (in case the cell changes later), you can change this
line:

..Cells(oRow, iCtr + 2).Value = wks.Range(myAddr(iCtr))
to
..Cells(oRow, iCtr + 2).Value _
= "=" & wks.Range(myAddr(iCtr)).address(external:=true)

mattbloodgood wrote:
>
> Dave - Thanks for the reply - I like the direction you are going but I
> need to be careful to associate rows on the summary page with the
> appropriate template copy. For instance, they are going to copy the
> Template and call it analysis 1. On the summary page, I am extracting
> several specific cells (e.g. A10, C10, E10, G10)from Analysis 1 and
> putting them all on the same row. In the next row, I am pulling the
> same cells from Analysis 2, on the third row, I'm pulling the same
> cells from Analysis 3 and so on for every template copy they make.
>
> Where I run into a problem is that I have no idea how many copies each
> team will ultimately make so the forumla needs to be flexible (which
> you've accomplished with the (start:finish helpers). My summary page is
> sourced from several other pages in the workbook including one where
> they describe qualitatively each of the different analysis types and
> provide an identifier - so is there some way that I can make this
> conditional on the Summary Row ID equaling the ID (contained in cell B2
> for instance) on each Analysis template?
>
> Thanks again -
> Matt
>
> --
> mattbloodgood
> ------------------------------------------------------------------------
> mattbloodgood's Profile: http://www.excelforum.com/member.php...o&userid=24999
> View this thread: http://www.excelforum.com/showthread...hreadid=385267

--

Dave Peterson