Help with Building Template, Repeatable Process

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wrldruler - 02 Feb 2007 18:18 GMT

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

Reply to this Message

Bernie Deitrick - 02 Feb 2007 19:27 GMT

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

> 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
[quoted text clipped - 30 lines]
>
> Chris

Reply to this Message