Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2001
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Worksheet autofill reference in formula (2000/sr1)

    I have a workbook with over 100 worksheets. In my recap worksheet I have a formula that does a calculation on the data in the 1st worksheet. I would like to use autofill to have Excel create the same formula for the second worksheet , third, etc. (i.e. is there relative referencing with worksheets like there is with rows and columns?) Or do I need to manually reference each worksheet? I am not a power uses and have not used code. Is this my only option? Would appreciate help.

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Worksheet autofill reference in formula (2000/sr1)

    You can download the trial version of an add-in from <A target="_blank" HREF=http://www.add-ins.com/>here</A> called Spreadsheet Assistant.

    It has the exact shortcut you're looking for among many others. I liked it, I bought it and I use it's many shortcuts and functions all the time. That's the quick fix - not the real solution. The real solution is to add the code that you need to excel without all the fluff that comes with an add-in program. It's probable that someone will post the necessary code before you can say "Woody"!

    But just in case...
    I've copied the actual description from the Assistant Function below...

    <font color=blue>Sum Across Sheets</font color=blue><font color=blue>

    If you have any worksheets and wish to sum the same cell across all sheets, you could do so manually by writing a formula that references each cell on each sheet. For example,

    =Sheet2!A1+Sheet3!A1+Sheet4!A1+Sheet5!A1+Sheet6!A1 +Sheet7!A1+Sheet8!A1+Sheet9!A1

    would be the formula necessary to sum cell A1 across Sheet2 through Sheet9. Which meant that you had to go to and click on cell A1 of each of these sheets.

    The Sum Across Sheets features creates a sum function that can not only sum cells across multiple sheets, but also ranges! And it does so very quickly and easily, saving you time.</font color=blue>[
    - Ricky

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Worksheet autofill reference in formula (2000/sr1)

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15> Don't know why I didn't think of it before...

    Just click the SEARCH link below the Woody's Lounge logo and type in <font color=red>SUM ACROSS SHEET</font color=red> in the search for box. The solutions have been posted before...
    - Ricky

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet autofill reference in formula (2000/sr1)

    I don't know which function you want to use, and on which range it should be applied. For this example I used the SUM function and the Range is A1:A4. The VBA code below contains a simple function that uses the Sheet's index as an argument. The function itself is restricted to 1 line:

    <pre>Function WkFunctionAcrossSheets(ShIndex As Integer)
    WkFunctionAcrossSheets = Application.WorksheetFunction. _
    Sum(Sheets(ShIndex).Range("A1:A4"))
    End Function
    </pre>


    If the sheets have consecutive indexes, then you can use the autofill. However, if you don't know the index that corresponds to the worksheet's name, you are better of using the sub below in combination with the above function:

    <pre>Sub PrintResults()
    Dim oSh As Worksheet
    Dim ShIndex As Integer
    Dim i As Integer

    i = 1
    ActiveSheet.Cells(i, 1).Value = "Index"
    ActiveSheet.Cells(i, 2).Value = "Name"
    ActiveSheet.Cells(i, 3).Value = "Result of SUM"
    For Each oSh In Worksheets
    If oSh.Name <> ActiveSheet.Name Then
    i = i + 1
    ActiveSheet.Cells(i, 1).Value = oSh.Index
    ActiveSheet.Cells(i, 2).Value = oSh.Name
    ActiveSheet.Cells(i, 3).Value = WkFunctionAcrossSheets(oSh.Index)
    End If
    Next
    End Sub
    </pre>



    If you have not used code yet, then you must know that going to Tools >> Macro >> Visual Basic Editor let you enter the VBE. In the Project Explorer, choose the VBAProject that corresponds to your workbook and add a general module (via Insert >> Module). Then copy the code for the function and the sub in that module. Here you can change the code; e.g. the range A1:A4 or the function SUM (nearly all Excel's built-in worksheetfunctions are allowed). Then return to your summary sheet in Excel and from Tools >> Macro >> Macros, select the sub and run it.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Worksheet autofill reference in formula (2000/sr1)

    Hi. Forgive me if you already know this. From what you describe, it sounds like you can simply replicate the formulas across all sheets using multiple sheets selected when pasting, and then fill-down with all the target sheets selected. Not all users are aware that with multiple sheets selected you can copy, paste and fill down in "3D". In other words, you can:

    1. copy the basic formula(s) from whatever source, select -all- the target worksheets within the workbook and paste to them all. (With this and the next step, the selected sheets cannot be protected.)

    2. with all those worksheets still selected, fill the pasted formula down; all the selected worksheets will now have your reproduced formula.

    3. If you need to edit those pasted and filled formulas, you can also Find-&-Replace in 3D through all selected sheets, with the caveat that Excel's limitation where Find-&-Replace on a single cell works on the entire worksheet also applies if you select a single cell on multiple selected worksheets. (Excel isn't smart enough to know you have selected multiple cells through the multiple worksheets.)

    If these 3D operation capabilities are new to you, practise on a dummy workbook until you understand how to use them; there are a few quirks in how copying and pasting works in 3D.

    Does this help?
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •