Adding a formula to the same cell (H5) on every tab

[John13]

I have an inventory spreadsheet with 125 tabs. The tabs are numbered
1 through 125. The are identical except for the data below the column
headings. If I wanted to put a formula in H5 on every tab, can it be
done other than manually opening every tab and typing it?

One additional question: If I add a Summary Tab, how could I show the
value of a specific cell on each tab without manually entering it? I
show the formula I'm using bring B3 to the summary for every tab:

A B
1 Unit Value
2 1 ='1'!B3
3 2 ='2'!B3
4 3 ='3'!B3
5 4 ='4'!B3
6 5 ='5'!B3
7 6 ='6'!B3
8 7 ='7'!B3
9 8 ='8'!B3
But to do it 125 times is a killer, besides I want to bring other
specific cells over to the summary also. I was thinking of
CONCATENATE if all else fails. Help please! Thanking you ahead of
time.

John

 

[Rick Rothstein \(MVP - VB\)]

I have an inventory spreadsheet with 125 tabs. The tabs are numbered

1 through 125. The are identical except for the data below the column
headings. If I wanted to put a formula in H5 on every tab, can it be
done other than manually opening every tab and typing it?

Assuming the formula is the same for each sheet, add this macro (to any
sheet's code window will do)...

Sub AddFormulaToH5()
Dim WS As Worksheet
For Each WS In Worksheets
WS.Range("H5").Formula = "=TEXT(NOW(),""mmmm, dddd"")"
Next
End Sub

You didn't tell us what your formula was, so I made one up for example
purposes. Now, go to the sheet where you placed the macro, press Alt+F8 and
run the macro. The formula should now be embedded in cell H5 of each sheet.
Delete the macro (it has done its job) and then do a save.

Rick

 

[Earl Kiosterud]

John,

You can put the formula in all the H5 cells by hand if the sheets are contiguous. Select
them all (Click the left-most, then scroll to the right-most and Shift-click it). If there
are sheets among them that should not be included, you can deselect them by Ctrl-clicking.
Select H5, type in your formula, and press Enter. That's it! Bob's your uncle. Be sure to
unselect the sheets before you continue -- anything you do with them selected happens to
all of them. Many filthy words have been uttered when people untentionally overwrote stuff
into multiple-selected sheets.

As for the summary, that's a big order with 125 sheets. Let me toss this in. Often, people
put stuff in separate sheets when putting them in one sheet would make life much easier, and
provide much more functionality (such as your summarizing). There's not much of a
down-side. Take a look at "Data across multiple sheets" at
http://www.smokeylake.com/excel/excel_truths.htm.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[Sandy Mann]

For the 125 formulas this works for me:

Option Explicit
Sub FillIt()
Dim x As Integer
Dim sTotal As Integer
Dim WS As Integer
Dim Nom As String
Dim cell As String
Dim cFormula As String

x = 2
sTotal = Worksheets.Count
For WS = 1 To sTotal - 1
Nom = Worksheets(WS).Name
cell = Cells(3, 2).Address
cFormula = "'" & Nom & "'!" & cell
Cells(x, 2).Formula = "=" & cFormula
x = x + 1
Next WS
End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Ragdyer]

To answer the second part of your question, one way is to simply create your
number list down a column, from 1 to 125, just as in your example, say from
A2 to A126.

Enter this formula in B2:

=INDIRECT(A2&"!B3")

And drag down to copy as needed.

 

[John13]

To answer the second part of your question, one way is to simply create your
number list down a column, from 1 to 125, just as in your example, say from
A2 to A126.

Enter this formula in B2:

=INDIRECT(A2&"!B3")

And drag down to copy as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !








- Show quoted text -

Thank you all very much. I love learning Excel. I tried them all and
fixed my problem. Thank you for your time.

John