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

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


--
mattbloodgood
------------------------------------------------------------------------
mattbloodgood's Profile: http://www.excelforum.com/member.php...o&userid=24999
View this thread: http://www.excelforum.com/showthread...hreadid=385267

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