variable worksheet references

[mattbloodgood]

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

 

[Dave Peterson]

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]

Dave - Thanks for the reply - I like the direction you are going but
need to be careful to associate rows on the summary page with th
appropriate template copy. For instance, they are going to copy th
Template and call it analysis 1. On the summary page, I am extractin
several specific cells (e.g. A10, C10, E10, G10)from Analysis 1 an
putting them all on the same row. In the next row, I am pulling th
same cells from Analysis 2, on the third row, I'm pulling the sam
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 eac
team will ultimately make so the forumla needs to be flexible (whic
you've accomplished with the (start:finish helpers). My summary page i
sourced from several other pages in the workbook including one wher
they describe qualitatively each of the different analysis types an
provide an identifier - so is there some way that I can make thi
conditional on the Summary Row ID equaling the ID (contained in cell B
for instance) on each Analysis template?

Thanks again -
Mat

 

[Dave Peterson]

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)