Home » Forums » Open Source Learning Center » Learning requests

Dragging Formula and only the Worksheet changing.

Submitted by Snoogy on 27 October, 2015 - 07:51

Hey Guys,

I have a question that I hope you are able to hepl me with.

I have multiple worksheets in my spreadsheet. I want to drag a formula down a column but only change the worksheet in the formula, due to there being so many worksheets, doing it manually would take a lifetime: For example.

Worksheets: A1, B1, C3, D4 ETC.

The formula I need to drag is: ='A1'!$D$15

Down the column should read: ='A1'!$D$15, ='B2'!$D$15, etc

Thanks for your help in advance!!

Snoogy

‹ How do you update the quantity for an inventory item that appears in several lists on the spread sheet Need to search between worksheets and consolidate on separate sheet. ›
»

Hi Snoogy, Give this Macro a

Submitted by Max F. on 29 October, 2015 - 23:37.

Hi Snoogy,

Give this Macro a shot (Insert your formula where it says $D$15, make sure you leave the quotation marks:

Sub Snoogy()
Dim NumWs As Integer
Dim Count As Integer

NumWs = Worksheets.Count
Count = 1
Do Until Count = NumWs + 1
    ActiveCell.Offset(Count - 1, 0) = "='" & Worksheets(Count).Name & "'!" & "$D$15"
    Count = Count + 1
Loop
End Sub

Let me know if this doesnt work or if you need anything else!

Sincerely,

-Max

»

Also

Submitted by Max F. on 29 October, 2015 - 23:39.

Also - If your formula contains multiple variables on other sheets, give me the actual formula you need and I'll build it out in VBA!

»