Thread: Formula with one changing variable: The Worksheet to which the formula is linked

Originally Posted by EHS

Hi,

I have a question concerning excel, and it concerns the following:

Ik have a large number of worsksheets, containing a table with the same type of information on different paintings. Meaning: for example in worksheet 1, there is in cell A1: "Titel", followed by cell B1: "Sunflowers". Followed by: "Painter" in cell A2, and "Van Gogh" in cell B2. This continues untill cell's A8-B8. Furthermore there is a similar line of information in cell's C2/D2 untill C8/B8 in the same lay-out (for example price of the painting, date of last exhibition, place of auction etc.)
Thus, the table contains 8 rows and 4 collumns. Every worksheet contains the same information, but on different paintings. Yet, it is all in the same lay-out and each cell (in different worksheets) contains the same information (f.e. cell B1 in all worksheets contains the title of the painting).

Now comes my problem. I want to have all information in the same worksheet, and in the following way: Row 1 contains a description of the data (Painter, Title painting, Date, Size, Genre etc.) and then there is in every row this information for every painting in every worksheet (f.e. row 2 for the Nightwatch by Rembrandt, row 3 for the Sunflower by van Gogh etc.)

My problem is that I have to many paintings and worksheets to manually place the formula in every cell. Can anyone help me out? What should I do?

A short summary: I need to replicate a formula with one changing variable: the worksheet to which the formula is linked, all the rest of the formula stays the same.

Thanks for your time and effort!

Regards,

A macro

Sub Macro1()
Dim ws As Worksheet
Dim ThisRow As Integer
ThisRow = 1
Sheets("Master").Select
For Each ws In ActiveWorkbook.Sheets
    If Not ws.Name = "Master" Then
        range("A" & ThisRow).Formula = "='" & ws.Name & "'!A1"
        range("B" & ThisRow).Formula = "='" & ws.Name & "'!B2"
        range("C" & ThisRow).Formula = "='" & ws.Name & "'!C1"
        range("D" & ThisRow).Formula = "='" & ws.Name & "'!D2"
        range("E" & ThisRow).Formula = "='" & ws.Name & "'!A4"
        range("F" & ThisRow).Formula = "='" & ws.Name & "'!B3"
        range("G" & ThisRow).Formula = "='" & ws.Name & "'!C2"
        range("H" & ThisRow).Formula = "='" & ws.Name & "'!D1"
        range("I" & ThisRow).Formula = "='" & ws.Name & "'!D8"
    ThisRow = ThisRow + 1
    End If
    Next
End Sub

will do that, you need to edit the macro to align the correct fields across the row.

It can be (re-)run at any time, as it does not amend data.

Save your work before trying this.

Insert a Worksheet called 'Master'

(alternately amend the macro to the name required)

hth
---