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
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
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,
Just traded in my OLD subtlety...
for a NUance!
RE: Drag Formula to increment worksheet
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
Skip,
Just traded in my OLD subtlety...
for a NUance!
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
RE: Drag Formula to increment worksheet
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Drag Formula to increment worksheet
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 Subthese 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
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 SubSkip,
for a NUance!