formula help #5

In a workbook of 31 pages. I am trying to add a totals page that will copy
the amount from one cell on each page. When trying to auto fill this formula
in a column, Excel will not automatically change the page numbers?
='1'!G$7*-1. Is there a way to make excel change the page numbers?
='1'!G$7*-1,  ='2'!G$7*-1, ='3'!G$7*-1 etc.


0
jimbeam27 (3)
1/12/2004 6:10:16 AM
excel 39879 articles. 2 followers. Follow

3 Replies
297 Views

Similar Articles

[PageSpeed] 56

Hi James!

You could use a couple of helper columns

Column A contains Sheet1 and will copy down incrementing the number
Column B contains the cell address and can copy down without the
number changing

Then you can use the formula in C:
=INDIRECT("'"&A1&"'!"&B1)*-1

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


0
njharker (1646)
1/12/2004 7:12:31 AM
James

I believe you mean "worksheets" when you refer to "pages".

Couple of methods.........

First......To sum all the G7 cells from all the sheets enter this formula in
your summary sheet, assuming it is the first sheet in the workbook.

=SUM(Sheet1:Sheet31!G7)

If your sheets are not in the 1,2,3 order, insert a new sheet to the right of
your Summary sheet.  Name it Start.  Insert a sheet at the end of your sheets.
Name it End.

In Summary sheet enter  =SUM(Start:End!G7)

Second.........

If your sheets are numbered 1 through 31 and you want to pull G7 from each to
the Summary sheet enter this formula in A1 of the Summary sheet.

=INDIRECT(ROW()& "!G7")

or if Sheet1, Sheet2 etc  =INDIRECT("Sheet" & (ROW() & "!G7") entered in A1 of
Summary sheet and dragged down 31 rows.

Gord Dibben Excel MVP


On Mon, 12 Jan 2004 06:10:16 GMT, "James Beam" <jimbeam27@earthlink.net>
wrote:

>In a workbook of 31 pages. I am trying to add a totals page that will copy
>the amount from one cell on each page. When trying to auto fill this formula
>in a column, Excel will not automatically change the page numbers?
>='1'!G$7*-1. Is there a way to make excel change the page numbers?
>='1'!G$7*-1,  ='2'!G$7*-1, ='3'!G$7*-1 etc.
>

0
Gord
1/12/2004 7:30:20 AM
try this. modify to suit
=INDIRECT("sheet" & ROW()&"!a1")

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"James Beam" <jimbeam27@earthlink.net> wrote in message
news:cTqMb.5314$1e.4799@newsread2.news.pas.earthlink.net...
> In a workbook of 31 pages. I am trying to add a totals page that will copy
> the amount from one cell on each page. When trying to auto fill this
formula
> in a column, Excel will not automatically change the page numbers?
> ='1'!G$7*-1. Is there a way to make excel change the page numbers?
> ='1'!G$7*-1,  ='2'!G$7*-1, ='3'!G$7*-1 etc.
>
>


0
Don
1/12/2004 2:54:45 PM
Reply:

Similar Artilces:

Help with SQL (Access2007)
Hello. I am trying to integrate data from several sites into 1 (new) table. In order to distinguish the data from each site in the new table I have a field (InstID) which holds the Instution number of the site. The fields from the old site tables and the new table are identical except for the InstID. InstID and ClientID are Primary Keys. The path to the old table is asked, then the number for the InstID is asked and placed as a variable - varInstID. I have an append sql as follows: Private Sub UpdateDB_Click() ' populate the clients table strSql = "INSERT INTO tblClients ( In...

forecast function help
This might seem a bit newbie but im having trouble with the forecas function. Say for example i have a collection of data for sales of each item ove a number of years: item 2000 2001 2002 2003 1 3 4 5 2 4 3 2 3 2 2 4 4 3 1 5 5 4 1 6 6 2 2 4 i am asked to forcast the values for the year 2003. I have read an looked at many examples on how to do this and still can't work ou where to start? Any help would ...

Formula not being stored any more
Recently, Excel has stopped storing certain formulae in the formula bar. For example, if I type in say "=3*1/10" Excel will store "=0.3" in the formula bar. This is most inconvenient as I want to be able to tell what the constituent parts of the calculation are. It never used to do this so have I accidentally set an option on somewhere? How do I turn it off again? I couldn't duplicate this. If I typed: =3*1/10 and hit F9 (calculate)--not enter I got: 0.3 You're not getting close to the F9 key (with only portions of your formula selected? (Yeah, I did...

Please Help!
Hi, hopefully someone can help me with this as I have drawn a blank. I have the following rental costs options on excel A B C D E 1 Street Monthly Quarterly Annual Cost 2 High St Y 3 Main St Y 4 Church St Y If I have the a reference sheet with st names and the rental costs in columns F (st names) G (monthly cost) H (quarterly cost) I (annual cost) What formula can I use to get column E to "Pick" the correct Street and costing from this data list? Thanks in advance for your help. Enter this in cell B2: =VLOOKUP($A2,'Reference Sheet Name'!$F:$I,C...

Days Old formula?
Hi im having a problem trying to figure out the forumla for days old. M teacher wants us to come up with a formula for the age of 2 dates. Does anyone know any formulas that will work? -- Message posted from http://www.ExcelForum.com If you need the days only, subtract =A1-A2 -- Regards, Peo Sjoblom "frackskat004 >" <<frackskat004.11hxa0@excelforum-nospam.com> wrote in message news:frackskat004.11hxa0@excelforum-nospam.com... > Hi im having a problem trying to figure out the forumla for days old. My > teacher wants us to come up with a formula for the age o...

Need help with Excel Form & ComboBox Tutorial
At http://www.excel-vba.com/v-forms-controls.htm I have followed instructions... my code on the form is below but it won't run... I've marked the error... Can anybody give me any help with this? thanks Code is below-------------- Private Sub cmdBtnSubmit_Click() shReport.Range("C4").Value = cbxCity.Value cbxCity.Value = "Select a City" frmCity.Hide End Sub Private Sub cmdCityCancel_Click() cbxCity.Value = "Select a City" frmCity.Hide End Sub Private Sub UserForm_Activate() shParameterst.Activate '<-----Run Time Error 424 - Object Required...

Customer Address Help
Hi, I was wondering if anybody knew of a way to run a customer report that invluced the customers address, city, state, and zip in it. I am not very familiar with crystal reports, so if there is another way that would be awesome. Thanks, -Bill H On CustomerSource there is a section called the "Report Library"- I think it's under downloads. In the RMS Report Library MS has provided several new or modified reports, including one with customer address. On Sat, 17 Jul 2004 08:40:10 -0700, Bill H <bill@platinumpools.com> wrote: > Hi, I was wondering if anybody k...

Macro Help/Duplicate Items + Insert Rows + Sum
I am trying to create a template that will do the following: 1. Find Duplicate Entries (AlphaNumeric) In A Column 2. Insert 2 Rows Between The Duplicate Entries Then: 1. Sub-Total(Another Column With Random Numbers) Of The Duplicate Entries 2. Format the Sub-Total In Bold I have gotten to the point of writting a macro that will identify the duplicate entries; does anybody know how to do the rest? This is a changing set of data, transferred to excel from a relational database (Lotus123 Rel2, which contains anywhere between 3000 to 5000 rows. I cannot spend time grouping the data ...

Thank You #5
I would also like to thank Dick and Carl for their years of assistance to this newsgroup as well as the FAQ site. ...

Viewing Formulas instead of formula results
I want to view all of the formulas in my worksheets without going through each cell and typing " " around each formula. Is there any way to do this so I can check all of my formulas at once? Meghan, here is one way, use Ctrl and ~, this will toggle between formulas and results -- Paul B Always backup your data before trying something new Using Excel 97 & 2000 Please post any response to the newsgroups so others can benefit from it ** remove news from my email address to reply by email ** "Meghan" <mmckee15@yahoo.com> wrote in message news:00db01c351f6$4eb0...

Help with graph / chart
I have a graph for weeks 1-52, I have split this into 4 seperate graphs each showing a quarter (13 weeks) I cant remember exactly how I created them but possibly using some sort of copy paste as each chart show weeks 1 - 13 along the bottom. This should read......... for chart 1 1-13 for chart 2 14 - 26 chart 3 27 - 40 chart 4 41 - 52 How do I change this on each chart to read the week numbers indicated.? thanks Hi, You need to define the Category labels for the chart. Chart 1 is fine as it defaults to the values 1 to 13. For the other 3 charts you will need to create...

Conditional Formula?
Hi- I need help creating a formula that sums values in a list based on the value in an adjacent cell. Please see attached screen shot. Hopefully it explains what I'm trying to do. Thanks. +-------------------------------------------------------------------+ |Filename: excel help 3-10-06.gif | |Download: http://www.excelforum.com/attachment.php?postid=4442 | +-------------------------------------------------------------------+ -- rhovey ------------------------------------------------------------------------ rhovey's Profile: http://www.excelf...

unable to load help topic
Using Money 2004 deluxe. Asking for help I get "unable to load topic" try again. No help, same responce. I went to MS Knowledge base article 812755. Which says 'clear the cache' Which I did. No help, still 'unable to load topic' Tried asking a 'Microsoft pro', could not get a screen to ask my question. Any suggestions? I cleared both MS IE and my default browser, and tried again, still no help. Seems like I should be able to get 'HELP' I even reloaded the Money program, still no HELP. Thanks for any 'HELP" Walt In microsoft.public.money...

Non responsive MTA on Exchange 5.5
Hello, I got the following problem: The MTA on an Exchange 5.5 box is for some reason not responding anylonger to a STOP service (probably due to a corrupted message in the queue). I want to have the least downtime possible and wanna run an MTACHECK asap. Two solutions available: -reboot the server (stopping the MTA doesn't work using the regular ways) -kill the MTA process using the KILL.EXE tool. It seems to me that killing the MTA process would be faster that having to restart the whole server. Moreover users can still access their mailboxes even though the MTA is running amok. ...

Can not create Matrix Item please Help RMS 2.0
RMS 2.0 Can not create Matrix Item please Help When trying to create any new items I receive error message This is the message (-2147217864) Row Cannot be located for updating. Some values may have been change since it was last read. Manger still creates standard items but still receives message with out this number in message -2147217864 ...

5.6.3 error
We have a monitoring program sending us daily reports for all our servers. It's been running fine until we just hit a "magic" numbers of how many servers we monitor. The email sent is in xml format, including 3 .png images per server, so I guess we just passed 250 in total images, which are considered attachments. How can I up the number of allowed attachments in Exchange? Can't find this setting anywhere...! TIA!! / Per Wouldn't it be better to put that stuff up on a web page? I've never heard of such a limit; you must really be pushing the envelope. You...

Massive Report: Have you ever done this? Help Please
Hi, I am compiling the results of a survey in ACC2003 as a paper appendix. I have about 60 report objects which are about 2 to 4 pages of text each. I have about 60 Pivot Charts and tables as separate form objects. I want to have one report which has the charts and tables and text in it since this would be easy to layout and the page numbering would flow right through. Is this the correct way to do it? I have made a start and the first few pages are fine with charts and tables. However, Access seems to have space restrictions on the height of a report group? When I increase the ...

SMTP Help!!!
I have a customer with a new Exchange 2003 server Single AD domain on one server DNS server local and seems to be working correctly Cable internet through Comcast Had been receiving 2012 and 2013 app log events Increased the DNSErrorsBeforeFailover as suggested in a knowledgebase article 2012 & 2013 Errors have stop but replaced with 4006 events No mail flow inbound or outbound for past 2 days!! SMTP appears busted, cannot telnet in or out on Port 25 even though firewall has port forwarding on that port Switched firewalls with same result Noticed periodic Back Orifice attack attem...

Macro Error, annoying plz help?
Hi, new to this board and kinda new to Excel as well. I created an excell file which includes various (difficult) calculations. And it's all finished and ready for distribution :P Cept for 2 minor things which i can't seem 2 fix. The most important one is this Macro Error which keeps popping up when you open the file. (I included a combo-box form, i think it's gotta do with that). Because if u pick something from that list (combo box) the error pops up again, very annoying of course. There is one way to prevent this as far as i could see and that was by setting macro security low....

CDateTimeCtrl #5
How do I allow a user to blank out a date when a date has already been entered into it and saved? "William Gower" <w_gower@hotmail.com> wrote in message news:uJQQYzHiDHA.2420@TK2MSFTNGP10.phx.gbl... > How do I allow a user to blank out a date when a date has already been > entered into it and saved? See if this kb article helps at all... http://support.microsoft.com/default.aspx?scid=kb;en-us;238077 Jeff... ...

How do you do cross worksheet formulae's
I've just started writing basic addition and multiplication formulae etc. for multiple cells. I want to know how to write formulae to d this across worksheets (I think thats what they are called - the tab at the bottom?) For exaple, how would I add A1 on sheet1 to A1 o sheet2 -- Liam Green ----------------------------------------------------------------------- Liam Greene's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=897 View this thread: http://www.excelforum.com/showthread.php?threadid=26786 Liam, if you formula is in sheet one, =A1+Sheet2!A1, if you f...

Help!!! Migration Problem!
Morning Guys, We're migrating from one Windows 2003 domain to another (acquisition). DomainA.lab - Forest Trust 2000, Domain Trust 2003 DomainB.lab - Forest Trust 2003, Domain Trust 2003 Migration from DomainA.lab to DomainB.lab - Trust relationship external, 2-way, Domain Wide Authentication Side Filtering disabled on both domain and I can also see the SID History attribute which is correct Problem: Users in domainA cant can't access SOME shares on domainB computers. The SIDHistory attribute in DomainB matches the SID of the group in DomainA, but still no luck. Any su...

Basic Worksheet Help
I can't find an auto sum function in google worksheet. Can some help? These are Excel groups -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS web: www.nickhodge.co.uk blog (non-tech): www.nickhodge.co.uk/blog/ <Hollins3@googlemail.com> wrote in message news:1180278623.923893.261800@u30g2000hsc.googlegroups.com... >I can't find an auto sum function in google worksheet. Can some help? > Hollins3@googlemail.com wrote: > I can't find an auto sum function in google worksheet. Can some help? > Easy workaround:...

Need help with code...
I have a problem. I have a drop down combo box called "Query status" with two options: "outstanding" and "completed". The record can't be changed to "completed" until certain other fields have ALL been entered but there is an extra complication. One other combo box can be either "other" or "invoice". Two extra fields need to be entered if this combo is "invoice" otherwise they aren't mandatory. In full this is the code I currently have in the "after update" event of each field: If Me.Qry_QryType =...

Help
The deal: On earthlink webmail, I only receive one of each email. When I "Send/Receive" from Outlook 2000, the Send/Receive window tells me I'm receiving 10 emails, but 30 appear in my inbox. Everything arrives in triplicate or duplicate. Extended headers appear to be the same in all three identical emails. Things I've tried and know: Deleted and then reconfigured my earthlink account many times. There is only one earthlink account. "Leave a copy of messages on server" is unchecked. Turned Earhlink Spamblocker On then Off then On then Off... no change. S...