×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

# Drag Formula to increment worksheet2

## 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,

Just traded in my OLD subtlety...
for a NUance!

### 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,

Just traded in my OLD subtlety...
for a NUance!

### 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,

Just traded in my OLD subtlety...
for a NUance!

### 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,

Just traded in my OLD subtlety...
for a NUance!

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

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!