Drag Formula to increment worksheet 2

[Jkaen]

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

 

[SkipVought]

Hi,

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 [tt]=INDIRECT(K6&"!$F$9")[/tt] and copy down.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[onlyadrafter]

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

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Jkaen]

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

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[onlyadrafter]

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

 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

 

[SkipVought]

"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.

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]