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

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

0
9/3/2007 6:20:44 PM
excel 39879 articles. 2 followers. Follow

5 Replies
661 Views

Similar Articles

[PageSpeed] 28

>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 

0
9/3/2007 6:43:42 PM
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...
-----------------------------------------------------------------------
"John13" <johnasmith13@gmail.com> wrote in message 
news:1188843644.564332.319870@r34g2000hsd.googlegroups.com...
>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
> 


0
someone798 (944)
9/3/2007 7:29:57 PM
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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"John13" <johnasmith13@gmail.com> wrote in message 
news:1188843644.564332.319870@r34g2000hsd.googlegroups.com...
>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
>
> 


0
sandymann2 (1054)
9/3/2007 7:31:41 PM
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 !
---------------------------------------------------------------------------
"John13" <johnasmith13@gmail.com> wrote in message
news:1188843644.564332.319870@r34g2000hsd.googlegroups.com...
> 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
>

0
ragdyer1 (4060)
9/3/2007 8:41:05 PM
On Sep 3, 4:41 pm, "Ragdyer" <RagD...@cutoutmsn.com> wrote:
> 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 !
> ---------------------------------------------------------------------------"John13" <johnasmit...@gmail.com> wrote in message
>
> news:1188843644.564332.319870@r34g2000hsd.googlegroups.com...
>
>
>
> > 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- Hide quoted text -
>
> - 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

0
9/4/2007 3:17:06 PM
Reply:

Similar Artilces:

Can you insert a picture into a specific cell?
Or do they only "float" on the page. Marc They float over the worksheet, but you can adjust the size so that it seems to fit a cell exactly. If you insert a picture, you can hold the alt-key down while you move/resize the picture. When it gets close to the edge of a cell, it'll snap-to that edge. Marc wrote: > > Or do they only "float" on the page. > > Marc -- Dave Peterson ...

Trouble with Tab Control
I have a form in which i have a tab on it. The problem is the tab appears white and I would like to show the background behind it. I saw in the properties there was a section to make it "transparent" or "normal" i've selected botha nd nothing changes. Any ideas on how to solve this. thanks On Jan 25, 9:32 am, tsla...@gmail.com wrote: > I have a form in which i have a tab on it. The problem is the tab > appears white and I would like to show the background behind it. I > saw in the properties there was a section to make it "transparent" or > ...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Extending formulas
Subject: Extending formulas Hi, For my application that uses Excel for calculations. I need to be able to extend the forula base of Excell with complex scientifc functions. Is there a way to add new functions to the Excel function base? Thanks Spx. MS has provided Visual Basic for Applications (VBA) to customize Excel with new functions, commands, forms, menus, etc. Tools|Macro|Visual Basic Editor From the VBA editor Insert Module Then write your functions in VBA. Details of writting functions in VBA is a very big topic, http://www.fontstuff.com/vba/vbatut01.htm may help y...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

How to unlock locked cells by MACRO?!
I have locked Sheet and I need to change particular cells. But when my macro wants to change it => ERROR :/ Is there any way to unprotect these particulary cells by macro and change them? - after that action, of course, I want to lock them again. Please help me, Pedro Just unprotect the sheet, do the work, and reprotect the sheet. Kind of like: activesheet.unprotect password:="yourpassword" 'your code to change stuff activesheet.protect password:="yourpassword" Petr N�me�ek wrote: > > I have locked Sheet and I need to change particular cells. But when m...

Adding a combo box to a worksheet
Hi all, I'm re-creating one of our paper forms in Excel and I'd like to add combo boxes to some blanks on the form to allow the user to choose a name from a list. I know a little about Excel formulas and no VB code at all...what's the idiot-proof way to do this? Thanks, Chris Hi Chris, The easiest way is to right-click within Excel in the toolbars area and select the "Forms" toolbar. Then Forms toolbar should then appear and could can select the "Combo Box" icon and click on that. If you can't tell which icon represents the Combo Box, just hover yo...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Symbol Updating Only Every Few Days (if at all)
Using Money 2006, and have a symbol "VLO" that is only updating every few days. This stock was a duplicate (downloaded transaction created a new version of the same stock - my fault not clicking correct choice when asked). I've removed the symbol from the stock entry that was downloaded, renamed this entry to something bogus, deleting this renamed stock "from all accounts", then added the symbol back to the original VLO stock that I've been tracking for years. Now the stock just says "unch" in the portfolio view, and the price history is only updat...

Adding a word to the end of other words at the same time
I was wondering if there was a way to add a word to the end or beginning of multiple other words in Excel. Example; say I have these 3 words.... Alpha Beta Tera Now I want to add LLC to the end of each word but I want to change them all at the same time. Like Alpha LLC Beta LLC Tera LLC Is there a way to do that? Phil Its Excel 2003 try Sub addtexttoend() For Each c In Selection c.Value = c & " xxx" Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "phil" <ptukey@charter.net> wrote in message news:1125340358.873337.4240@g44g2000cwa.googlegroup...

Adding a Macro to a VLookup Function
In Excel 2000 -- I would like to create a summary spreadsheet (sheet2). For each time that "Name" appears in sheet 1, row *, take information from the same row, but columns 2 and 6 and bring it over to sheet 2 in the designated area. I know that you need to do a "VLookup" function and I got that to work. I know that you also need a "Loop" statement, so that it will continue to perform the Vlookup and bring over the information for each time that the name appears. I don't know how to write the formula so that the VLookup and the Loop are combined. Examp...

Need to add to current formula
I have this formula that will cause values to change based on the mont that is referenced in the formula ($L$1). Currently the formul is:=VLOOKUP($A$1,$AD$7:$AG$44,IF($L$1="January",2,IF($L$1="February",2,IF($L$1="March",2,IF($L$1="April",2,IF($L$1="MAY",4,IF($L$1="June",3,IF($L$1="July",3,0))))))),0) I need to add August, September, October, November, & December to thi formula but excel is not allowing me. Does anyone know how I can get around this? Oh by the way November thru April =2, May and October=4 and June thr...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

Adding blank "separator" Rows in a Pivot Table
Does anyone know how to insert blank rows into a pivot table (say, between groupings, etc.)? There has to be a way, since some of the various table formats that come with Excel include blank rows. Double-click the field button that you want to format Click Layout Add a checkmark to Insert blank line after each item Click OK, twice Bucko wrote: > Does anyone know how to insert blank rows into a pivot table (say, > between groupings, etc.)? There has to be a way, since some of the > various table formats that come with Excel include blank rows. -- Debra Dalgleish Contextures http...

Formula Problem?
I am using Excel 2000 with Windows XP. I am having a problem. I am on Sheet 2 of my workbook. I have SSN on a sheet named Employees in the same workbook. I need to take the numbers on the Employees Sheet and transfer it to the sheet 2. I know how to do this. It just won't work. This is a copy of my formula. =SUM(Employees!C3) This should take the SSN that is in the C3 cell on the employees sheet and place it at the cell where the formula is typed. When I put this formula in the cell I am getting just a "0". Please help. =Employees!C3 -- Kind regards, Niek Otten...

RPC Over HTTP on Single Server
I have installed Exchange 2003 SP1 on a single server and installed and configured OWA. In following KB 833401, it says to add reg. entries to the \NTDS service. I don't have Active Directory installed on the computer. How do I ensure a proper configuration (see below), when this option is not avail. since I did not install AD, as suggested, on the Exchange 2003 server. "Configure all your global catalogs to use specific ports for RPC over HTTP for directory services" HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\NTDS\Parameters\NSPI interface protocol sequences...

formula auditing/macro
Can anyone give me the sytax to goto - special - precedents so I can create a macro so I can assign to a hotkey and dont have to go through 4 steps ? Thanks, Yosef With A1=1 and D2=2*A1, and D1 as active cell: I recorded a macro for these steps: Edit|GoTo->Special->Precedence And the macro contained just one line: Selection.DirectPrecedents.Select best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ynissel" <ynissel@discussions.microsoft.com> wrote in message news:DA544BDE-3717-4953-A5E3-06191BC28373@microsoft.com... > Can anyone...

formula to have 0 to 6.5 only in ref to other cell val of 0<=6.5
...

SQL query / Formula for Reorder Point?
Is it possible to issue an SQL command(s) that would: 1. Set the Restock Level for all items to be equal to the current on-hand quantity for each item. 2. Set the Reorder point for all items to be equal to 1/3 of that items Restock Level. I'm currently doing this manually, or using a formula in excel when items are initially imported into the DB, but there are still several thousand that need to be retro-fitted like this... Thanx in advance! -=RFM=- UPDATE Item SET RestockLevel=(Quantity - QuantityCommitted) UPDATE Item SET ReorderPoint=ROUND(RestockLevel/3,0) - Evan Culver Ne...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...

Help with percent formula beginner
Hi, have a cell (A1) with $39.99. I want a cell (B1) were I can vary 10%, 20% etc and have that effect (A1). So If I put in 10% A1 would be $36.00 (percent decrease). Can you please help me out with the formula? Also it seems like if I type in % in a cell and I delete it and type another number and I don't want a % in there it gives it to me anyway. Can I make this stop? Thanks so much Ted Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B1")) Is Nothing Then Wi...

changing a cell of 60 files
Hi all I've 60 files and another one which summarizes all of them.. I've to put a day in the cell a1 and then I'd like to cut and paste that day in the cell a1 of the other 60 files without having to change all of them manually. I'd like to save and then exit every single file.The files are named 0001 0002 0003..and so on. I know that this is possible with a macro..but I've got a problem.. It's possible not to have the prompt which asks for updates of the file everytime I open one of them?? Thanks for the help Rossella Hi Rossella http://www.rondebruin.nl/copy4.htm ...