Help with Building Template, Repeatable Process

[wrldruler]

I've got a survey tool that exports survey results as Excel. It puts
each question on a seperate worksheet tab. The export always has the
same naming scheme and structure everytime. I can't change the survey
export. So my tabs looks like:

Question_1
Question_2
Question_3....

I created a "Master Summary" worsksheet that summarizes the results of
each question tab onto one page.

So my cell references look like:

='Question_1'!A6
='Question_2'!A6
='Question_3'!A6...

The problem is that I cannot come up with a process by which I can
export a fresh file, insert the "Master Summary" tab, and it
automatically connects to the tabs.

My cell refrences on my Master Summary get screwed up. I either get
#REF, or it links back to the original filename, etc.

My users could open up the template, and paste in the new VALUES each
time, thus not moving any tabs, but they would have to do it for every
single question tab, and that's a pain.

Anybody have any suggestions?

Thanks,

Chris

 

[Bernie Deitrick]

Chris,

A macro will work great.... Run the macro below while the workbook with the Question sheets is
active.

HTH,
Bernie
MS Excel MVP

Sub CreateLinks()
Dim i As Integer
Worksheets.Add before:=Sheets(1)
ActiveSheet.Name = "Master Summary"
Range("A1").Value = "Sheet name"
Range("B1").Formula = "Cell A6 Value"

For i = 2 To Worksheets.Count
Range("A" & i).Formula = Worksheets(i).Name
Range("B" & i).Formula = "='" & Worksheets(i).Name & "'!A6"
Next i
End Sub