INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Drag Formula to increment worksheet

Drag Formula to increment worksheet

(OP)
Hi all, I have a tracking spreadsheet set up to track various procurement activities with each package as a separate worksheet.

I then have an index page with hyperlinks to the individual worksheets for summary purposes.

What I want alongside these links is to pull up various data from the main pages. The worksheets are renamed, but essentially what I want is the following:

Cell in Index Page ----- Formula in cell

k6 ---- =Sheet2!$F$9
k7 ---- =Sheet3!$F$9
k8 ---- =Sheet4!$F$9
k9 ---- =Sheet5!$F$9
k10 ---- =Sheet6!$F$9

This is for about 100 sheets,and is a common problem I hit on a number of other spreadsheets. Does anybody know how I can quickly 'drag' the formula down in such a way it increments the worksheet?

Stephen

RE: Drag Formula to increment worksheet

Hi,

Quote:

Hi all, I have a tracking spreadsheet set up to track various procurement activities with each package as a separate worksheet
That is your first mistake. Having separate sheets for similar data makes data analysis and reporting very difficult.

As far as incriminating sheets, enter Sheet1 in the first cell and drag down as far as needed. So if Sheet1 is in K6, then in L6 enter =INDIRECT(K6&"!$F$9") and copy down.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Drag Formula to increment worksheet

2
Hello,

no need for two cells, as long as your tabs are Sheet2, Sheet3 etc then on the index page in Cell K6 enter this formula:

=INDIRECT("Sheet"&ROW()-4&"!$F$9")

and copy down as far as required

----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back

RE: Drag Formula to increment worksheet

Good tip, oad. Why didn't I think of that! 😡 Have a star! 😉

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Drag Formula to increment worksheet

(OP)
Thanks for the responses, I know multiple sheets are generally frowned upon, its just occasionally its difficult to do otherwise. Seems i made a mistake renaming the tabs too early to make onlyadrafters solution to work.

Think the answer is to rework things in such a way I can get it all on 1 sheet

RE: Drag Formula to increment worksheet

Perhaps you could state the issue more clearly and maybe upload a sample of your workbook, illustrating the problem.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Drag Formula to increment worksheet

Hello,

if you are still going with the multi sheet option, there are a couple of ways you can still get the sheet tab names and the link to the cell

CODE

Sub GET_TAB_NAMES()
    For MY_SHEETS = 2 To ActiveWorkbook.Sheets.Count
        Cells(MY_SHEETS + 4, 11).Value = Sheets(MY_SHEETS).Name & "!$F$9"
        Cells(MY_SHEETS + 4, 12).Formula = "=indirect(K" & MY_SHEETS + 4 & ")"
    Next MY_SHEETS
End Sub

Sub GET_TAB_NAMES_1()
    For MY_SHEETS = 2 To ActiveWorkbook.Sheets.Count
        Cells(MY_SHEETS + 4, 11).Value = Sheets(MY_SHEETS).Name
        Cells(MY_SHEETS + 4, 12).Formula = "=indirect(K" & MY_SHEETS + 4 & "&""!$F$9""" & ")"
    Next MY_SHEETS
End Sub 

these codes assume your INDEX page is the left most tab and the rest are the 'linked' tabs.

----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back

RE: Drag Formula to increment worksheet

"Think the answer is to rework things in such a way I can get it all on 1 sheet"

You are correct. If you're interested in doing that, here's a plan to accomplish that task. Quite a coincidence that I just this week consulted with a former colleague on an identical problem. Having ALL your data in one table will make analysis and reporting so much easier, because Excel's formulas and features are designed for single tables.

PLAN: First add a sheet named AllPackages with appropriate table headings plus one for the heading, PackageID. The procedure would loop through each sheet in the workbook, excluding consolidation for any named sheet(s). Consolidation would copy the table on each sheet, excluding the heading row, and then paste into AllPackages in the next available row. Then assign the SheetName in the PackageID column.

CODE

Sub ConsolidateSheets()
'SkipVought Eng-Tips 9/17/2016
'This procedure does not destroy and sheets in this workbook or any data in existing sheets.
'PLAN: YOU MUST FIRST add a sheet named AllPackages with appropriate table headings
'      plus one column for the heading, PackageID.
'
    Dim ws As Worksheet, rPackageID As Range
    
    Application.ScreenUpdating = False
    
    With Sheets("AllPackages")
        Set rPackageID = .Cells(1, 1).End(xlToRight)            'Identify the PackageID column
'Loop through each sheet in the workbook
        For Each ws In ActiveWorkbook.Worksheets
            Select Case ws.Name
                Case "Summary", "AllPackages"                   'exclude these sheets
                Case Else                                       'consolidate
                'Copy the table on each sheet, excluding the heading row
                    ws.Select
                    Intersect(ws.UsedRange, Range(ws.Cells(2, 1), ws.Cells(2, 1).End(xlDown)).EntireRow).Copy
                'Paste into AllPackages in the next available row
                    .Cells(.Cells(1, 1).CurrentRegion.Rows.Count + 1, 1).PasteSpecial xlPasteValues
                'Assign the SheetName in the PackageID column
                    .Select
                    Intersect(Selection.EntireRow, rPackageID.EntireColumn).Value = ws.Name
            End Select
        Next
    End With
    
    Set rPackageID = Nothing
    Application.ScreenUpdating = True
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close