Filling cells referencing multiple sheets

DJRenegade

New Member
Joined
Nov 7, 2002
Messages
6
Is there an easy way to autofill cells when referencing multiple sheets, without having to change each sheet reference in the cells.

i.e In my workbook every sheet represents a day in the month. The 32nd sheet summarizes the stats from the sheets. All 31 sheets are laid out identically.
In my summary sheet column A are dates in the month Column B will refrence Cell D3 to the corresponding date.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
L

Legacy 98055

Guest
<pre>
Hi DJ.
Found your post buried with no replies. You really need to provide some more details or else
you leave everyone to guess and a guess usually equals a waste of time. Answer these questions
and I assure you that a solution will be posted.

1. What range in Column 'A' contains your dates? (Assuming that each date refers to each individual
worksheet(1 to 31).
2. How are your sheets arranged(1 to 31 = days of the month?) Again, this is only an assumption.

I don't know of any native Excel functionality which will fill down references based upon sheet index.
This bit of code may or may not work for you. Based upon my own assumptions of your workbook's
structure. The variable "First_Row_In_Column_A" is included to make the routine easier for you to edit.
The value of 2 should be changed to the actual first row of listed dates in your summary sheet. Make sure
your summary sheet is the active sheet when running this macro.

Sub AutoFillSheetNames()
Dim i As Integer, First_Row_In_Column_A As Long
First_Row_In_Column_A = 2
For i = 1 To 31
Cells(i, 2).Value = "=" & Sheets(i).Name & "!D3"
First_Row_In_Column_A = First_Row_In_Column_A + 1
Next
End Sub

tom
</pre>
 

DJRenegade

New Member
Joined
Nov 7, 2002
Messages
6
Thanx for the reply Tom
Here are some more details:

Sheet 1 representing the first day of the month is named Industrial (1), sheet 2 representing the second day is named Industrial (2), and so on and so forth upto the 31st sheet.
All 31 sheets are Identically laid out.
Column A represents a different route, column I are hours of service corresponding to each route.
In my summary sheet Column A are days of the week corresponding to Dates of the Month in column B(ie. Tue-Oct1, Wed-Oct2, Thu-Oct3, etc.). Columns C to CZ are all the routes.
Cell C2 in the summary sheet looks like this ='Industrial (1)'!$I$2

Now the question is: How do I get that 1 in parenthesis to count up when I fill the subsequent cells.
 
L

Legacy 98055

Guest
I don't know DJ?
If nobody can help you, use this UDF. Paste it in any standard module. Use it like a normal function. Instead of typing in ='Industrial (1)'!$I$2 as in your example, you would type in =sh(1,"I2")
The one could be replaced with a range if you wanted.
tom
 

Whisperer14

Well-known Member
Joined
Nov 6, 2002
Messages
589
The following code in Sheet 32 will copy the entry in cell A1 of each sheet into Column A of sheet 32.

Hope that helps, just change the 1 to the appropriate column number and build from there.

If you need more specific help then feel free to email me.

KR :)

Sub SummariseSheet()
For i = 1 To 31
Cells(i, 1) = Sheets(i).Cells(1, 1)
Next i
End Sub
 

DJRenegade

New Member
Joined
Nov 7, 2002
Messages
6
Thanx Tom and KR

But I knew if I just thought about it long enough the logic would come to me.
I used transpose in the paste special.
I took the reference ='Industrial (1)'!$I$2
drop the dollar sign in front of the 2 filled the subsequent cells beneath to correspond to each route
Copied then transposed then moved to the corresponding date row.
I recorded the Macro from fill to Transpose.
Created the button and voila:)
Now I just have to do this maneuver 30 more times as opposed to changing 3100 cells. Whew!

Thanx again
My next project is utilizing the VLOOKUP.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top