AutoFill Changing Wrong Value

I'm trying to create an analysis sheet that gathers data from 100 worksheets 
within the same workbook. 

I want the same cell reference in each sheet, but to change the sheet number 
in each cell. 

The worksheets are named 1 - 100

Here is the formula: ='1'!B3:E3

It changes the cell references only, not the worksheets. 

I've changed the formula to keep the cell references: ='1'!$B$3:$E$3

I now need the formula to increment the sheet number each time, PLEASE!!!

Thanks, Lisa
0
Utf
3/9/2010 9:37:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

6 Replies
1012 Views

Similar Articles

[PageSpeed] 42

Presumably you want to sum data from that range? Try this:

=3DSUM(INDIRECT("'"&ROW(A1)&"'!B3:E3"))

then copy that down as far as you need.

Hope this helps.

Pete

On Mar 9, 9:37=A0am, Lisa <L...@discussions.microsoft.com> wrote:
> I'm trying to create an analysis sheet that gathers data from 100 workshe=
ets
> within the same workbook.
>
> I want the same cell reference in each sheet, but to change the sheet num=
ber
> in each cell.
>
> The worksheets are named 1 - 100
>
> Here is the formula: =3D'1'!B3:E3
>
> It changes the cell references only, not the worksheets.
>
> I've changed the formula to keep the cell references: =3D'1'!$B$3:$E$3
>
> I now need the formula to increment the sheet number each time, PLEASE!!!
>
> Thanks, Lisa

0
Pete_UK
3/9/2010 9:58:57 AM
Not sure what do you want because your formula ='1'!$B$3:$E$3 gives a #VALUE 
error when entered in a single cell.

If you want to collect single cell values to single cells, e.g.
='1'!$B$3

in row2 of the summary sheet and you want to change sheet names when the 
formula is filled down then use this:

=INDIRECT("'"&ROW()-1&"'!B3")


Otherwise please clarfy your request!

-- 
Regards!
Stefi



„Lisa” ezt írta:

> I'm trying to create an analysis sheet that gathers data from 100 worksheets 
> within the same workbook. 
> 
> I want the same cell reference in each sheet, but to change the sheet number 
> in each cell. 
> 
> The worksheets are named 1 - 100
> 
> Here is the formula: ='1'!B3:E3
> 
> It changes the cell references only, not the worksheets. 
> 
> I've changed the formula to keep the cell references: ='1'!$B$3:$E$3
> 
> I now need the formula to increment the sheet number each time, PLEASE!!!
> 
> Thanks, Lisa
0
Utf
3/9/2010 10:29:01 AM
hi, 

Thanks for that. I altered it to read:

=INDIRECT("'"&ROW()-13&"'!B3:E3")

It works!
Thanks and Regards, Lisa

"Stefi" wrote:

> Not sure what do you want because your formula ='1'!$B$3:$E$3 gives a #VALUE 
> error when entered in a single cell.
> 
> If you want to collect single cell values to single cells, e.g.
> ='1'!$B$3
> 
> in row2 of the summary sheet and you want to change sheet names when the 
> formula is filled down then use this:
> 
> =INDIRECT("'"&ROW()-1&"'!B3")
> 
> 
> Otherwise please clarfy your request!
> 
> -- 
> Regards!
> Stefi
> 
> 
> 
> „Lisa” ezt írta:
> 
> > I'm trying to create an analysis sheet that gathers data from 100 worksheets 
> > within the same workbook. 
> > 
> > I want the same cell reference in each sheet, but to change the sheet number 
> > in each cell. 
> > 
> > The worksheets are named 1 - 100
> > 
> > Here is the formula: ='1'!B3:E3
> > 
> > It changes the cell references only, not the worksheets. 
> > 
> > I've changed the formula to keep the cell references: ='1'!$B$3:$E$3
> > 
> > I now need the formula to increment the sheet number each time, PLEASE!!!
> > 
> > Thanks, Lisa
0
Utf
3/9/2010 10:41:02 AM
Thanks, I have combined what both posts advised and ended up with this for 
the sum cells:

=SUM(INDIRECT("'"&ROW()-13&"'!E98"))

Thanks and Regards, Lisa

"Pete_UK" wrote:

> Presumably you want to sum data from that range? Try this:
> 
> =SUM(INDIRECT("'"&ROW(A1)&"'!B3:E3"))
> 
> then copy that down as far as you need.
> 
> Hope this helps.
> 
> Pete
> 
> On Mar 9, 9:37 am, Lisa <L...@discussions.microsoft.com> wrote:
> > I'm trying to create an analysis sheet that gathers data from 100 worksheets
> > within the same workbook.
> >
> > I want the same cell reference in each sheet, but to change the sheet number
> > in each cell.
> >
> > The worksheets are named 1 - 100
> >
> > Here is the formula: ='1'!B3:E3
> >
> > It changes the cell references only, not the worksheets.
> >
> > I've changed the formula to keep the cell references: ='1'!$B$3:$E$3
> >
> > I now need the formula to increment the sheet number each time, PLEASE!!!
> >
> > Thanks, Lisa
> 
> .
> 
0
Utf
3/9/2010 11:09:01 AM
You are welcome! Thanks for the feedback, but I still don't understand how 
=INDIRECT("'"&ROW()-13&"'!B3:E3") could work. It works as a SUM range like 
Pete presumed, but not in itself.

Clicking the YES button will be appreciated.

-- 
Regards!
Stefi



„Lisa” ezt írta:

> hi, 
> 
> Thanks for that. I altered it to read:
> 
> =INDIRECT("'"&ROW()-13&"'!B3:E3")
> 
> It works!
> Thanks and Regards, Lisa
> 
> "Stefi" wrote:
> 
> > Not sure what do you want because your formula ='1'!$B$3:$E$3 gives a #VALUE 
> > error when entered in a single cell.
> > 
> > If you want to collect single cell values to single cells, e.g.
> > ='1'!$B$3
> > 
> > in row2 of the summary sheet and you want to change sheet names when the 
> > formula is filled down then use this:
> > 
> > =INDIRECT("'"&ROW()-1&"'!B3")
> > 
> > 
> > Otherwise please clarfy your request!
> > 
> > -- 
> > Regards!
> > Stefi
> > 
> > 
> > 
> > „Lisa” ezt írta:
> > 
> > > I'm trying to create an analysis sheet that gathers data from 100 worksheets 
> > > within the same workbook. 
> > > 
> > > I want the same cell reference in each sheet, but to change the sheet number 
> > > in each cell. 
> > > 
> > > The worksheets are named 1 - 100
> > > 
> > > Here is the formula: ='1'!B3:E3
> > > 
> > > It changes the cell references only, not the worksheets. 
> > > 
> > > I've changed the formula to keep the cell references: ='1'!$B$3:$E$3
> > > 
> > > I now need the formula to increment the sheet number each time, PLEASE!!!
> > > 
> > > Thanks, Lisa
0
Utf
3/9/2010 12:07:01 PM
Thanks for the feedback, Lisa. You seem to have changed the details.

Note that if you use ROW(A1) instead of ROW()-13 in your formula, then
this will return 1 whichever row it is in, whereas the second term is
dependent on which row you put it in.

Pete

On Mar 9, 11:09=A0am, Lisa <L...@discussions.microsoft.com> wrote:
> Thanks, I have combined what both posts advised and ended up with this fo=
r
> the sum cells:
>
> =3DSUM(INDIRECT("'"&ROW()-13&"'!E98"))
>
> Thanks and Regards, Lisa
>
>
>
> "Pete_UK" wrote:
> > Presumably you want to sum data from that range? Try this:
>
> > =3DSUM(INDIRECT("'"&ROW(A1)&"'!B3:E3"))
>
> > then copy that down as far as you need.
>
> > Hope this helps.
>
> > Pete
>
> > On Mar 9, 9:37 am, Lisa <L...@discussions.microsoft.com> wrote:
> > > I'm trying to create an analysis sheet that gathers data from 100 wor=
ksheets
> > > within the same workbook.
>
> > > I want the same cell reference in each sheet, but to change the sheet=
 number
> > > in each cell.
>
> > > The worksheets are named 1 - 100
>
> > > Here is the formula: =3D'1'!B3:E3
>
> > > It changes the cell references only, not the worksheets.
>
> > > I've changed the formula to keep the cell references: =3D'1'!$B$3:$E$=
3
>
> > > I now need the formula to increment the sheet number each time, PLEAS=
E!!!
>
> > > Thanks, Lisa
>
> > .- Hide quoted text -
>
> - Show quoted text -

0
Pete_UK
3/9/2010 2:02:09 PM
Reply:

Similar Artilces:

Disabled "auto reply"
Howdy all. I'm hoping someone might be able to help me out. We're running an Exchange 2003 server using Outlook clients internally and OWA for remote access. POP is disabled for remote access. I've checked the Global Settings -->IMF settings in the ESM and confirmed that "allow out of office replies" and "allow automatic replies" are deselected. I've checked the rules within Outlook and confirmed that no rules exist. However, I'm still seeing auto replies being sent to Internet accounts (not from within the network when connecting via Outlook)...

Daylight Savings Time Changes
Hello, Does anyone know if the patch for Exchnage 2003 identified in KB926666 has to also be applied to every server that is running the Exchange Tools (System Manager)? Thanks in advance K On Wed, 31 Jan 2007 10:24:03 -0800, Kerry <Kerry@discussions.microsoft.com> wrote: >Hello, > >Does anyone know if the patch for Exchnage 2003 identified in KB926666 has >to also be applied to every server that is running the Exchange Tools (System >Manager)? > >Thanks in advance >K Im all for consistency. If the patch isnt needed, it shouldnt install, so I would at ...

Changing color of a range of cells dending up on a value in anothr
Hi, I need to change the color of a range of cell, depending up on the value in another cell. I am using Excel 2003. And it allows me to give only 3 conditions when using conditional format. But I have 7 differnt conditions. Thanks in advance for any help. Srajes. Unless you move to XL2007, you will need to use VBA. Here are some sites that will help http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm http://www.mvps.org/dmcritchie/excel/condfmt.htm Alternatively, there is an add-in here http://www.xldynamic.com/source/xld.CFPlus.Download.html -- Steve "Srajes&qu...

Changing the default folder when inserting files.
Does anybody know how to change the default folder Outlook opens when inserting files? I have already tried the registry with no luck. Thanks. you can change the default folder - use the places bar to select the folder you want. http://www.poremsky.com/p/places_bar.htm -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com "Kristin" <anonymous@discussions.microsoft.com> ...

How do I change the "Project Type" in "law Firm Financial Analys..
he "Lawfirm Financial Analysis Worksheet" currently has only attorney related "Projects Types," e.g., bankruptcy, forming corporations, amublence chasing. I'd like to modify the worksheet so it would fit other professions, i.e., engineering, consulting, and the like. Are these worksheets, including the law firm financial analysis worksheet, modifyable? If you download the worksheet: http://office.microsoft.com/en-ca/templates/TC011406201033.aspx you should be able to modify it. Clear the existing project types, and replace them with the ones you need. seanac...

Ending balance doesn't equal market value
I am using MS Money 2004. In my portfolio the Contributions ($) Market Value amount is showing a negative amount, but Today's Balance shows a zero Ending Balance. How do I get them to both show tha same number? In microsoft.public.money, Eggman wrote: >I am using MS Money 2004. In my portfolio the Contributions ($) Market Value >amount is showing a negative amount, but Today's Balance shows a zero Ending >Balance. How do I get them to both show tha same number? Try File->RepairMoneyFile->QuickFileRepair Look for future transactions. ...

Outlook Favorites Won't Save, Messages wrong on Outlook Today #2
Re-posting a message from a couple of weeks ago, hoping for better luck this time. I have Outlook 2003 running on XP Pro. Original message, with some additional info in brackets: A few days ago, I lost all my favorites. Also, the Messages section on Outlook Today no longer shows the number of unread messages, and if I click on a folder under Messages, nothing happens. For the Favorites, once I add them back in, it works fine until I reboot, and then they are gone again. I do close and re-open Outlook after adding my folders, so it should be saved properly, and it appears to be working w...

ok , i'm making name tags for an event....have to change the name
here is my problem....I am doing name crads.... so i have to change the name on each crad.....and i am lost as how to do that.....and then they all print out with the different names...Thank you... Fran You will have to build a data base and use mail merge. Help here: http://office.microsoft.com/en-us/publisher/CH100502901033.aspx -- Mary Sauer http://msauer.mvps.org/ "Fran Buettner" <Fran Buettner@discussions.microsoft.com> wrote in message news:5B872C0E-4FDA-4899-80DA-95609AA63623@microsoft.com... > here is my problem....I am doing name crads.... so i h...

Lowest value in a selection of data
Hi What formula would I use to find the lowest value in a selection of data i.e. B49:E52. Much Appreciated Brian =MIN(B49:E52) -- Gary''s Student - gsnu2007a Jumping in here... Is it possible to know the address of the cell containing the minimum value? John. You can use the MATCH function to locate the (relative) position, but if you have more than one value which are minima then it will only find the first. Pete On Nov 16, 7:23 pm, John Google <JohnGoo...@hotmail.co.uk> wrote: > Jumping in here... > > Is it possible to know the address of the cell containin...

changing accounts for sent emails
I am using Outlook 2000 with my laptop. I receive and send email from two differents accounts. One is for work, the other is for personal email. The default send is for the work account. I know to change the send account using the options choice on the toolbar, however, I frequently forget to do that. Is there a way to either: 1) display the name of the from: account when composing the email (which I usually do with Microsoft Word) so that I can more easily remember to change the account or 2) associate some contacts preferentially with the non-default account (i.e. have one default accou...

How to change background color of a window that has been created.
I need to change the background of a window that has been created, can you help? Hello Jerry, > I need to change the background of a window that has been created, can > you help? > Answer depends upon the type of window. Typically you can do this in OnEraseBkgnd. Certain controls respond to WM_CTLCOLOR and yet some other, like CButton, need to be owner drawn to change the color. If you have a generic CWnd, you can always specify the brush in the window class; so no special painting is needed. This is good when you know that the background will have one color all the time. -...

Adding Values From Different Tabs
Is there a way to add values from different tabs on the same spreadsheet? For example A2 from tab 1 and A2 from tab 2 -- Flipkid2 ------------------------------------------------------------------------ Flipkid2's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17984 View this thread: http://www.excelforum.com/showthread.php?threadid=398184 Assuming the name on Tab 1 is Sheet1 and Tab 2 is Sheet2, use thi formula: =Sheet1!A2+Sheet2!A2 note the name of the sheet is followed by "!" to indicate it is a shee name. HTH Bruc -- swatsp0 ------------...

Change order of fields in a report to a custom arrangement
In a report, I want to list sections in a certain way that is not alphabetical or numerical. Is there a way to do this? Curerntly, it is alphabetical as below: **Staff Section** Chief of Staff Command Counsel G1 G2 G3 Public Affairs Special Staff ** I would like for the fields to be organized as below: ***Staff Section*** G1 G2 G3 Chief of Staff Command Counsel Special Staff Public Affairs Thanks! Glen Try going to Sorting and Grouping. It is one of your buttons on the command bar. You should be able to set it anyway you wish. -- Milton Purdy ACCES...

Checking which fields changed
Hi Is it possible to identify individual fields changed by a user during editing on a form before or immediately after the form is saved? Thanks Regards On May 9, 4:25 pm, "John" <J...@nospam.infovis.co.uk> wrote: > Hi > > Is it possible to identify individual fields changed by a user during > editing on a form before or immediately after the form is saved? > > Thanks > > Regards I assume you mean what data was changed when you save the record? If so look at the OldValue property of your text boxes etc. Take a look at what Allen Browne has at ...

Excel: Open Recent Files
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I can't get the list to change. It continues to list the same 10 files all the time. <br><br>If I delete the Microsoft Preferences file, i.e. com.microsoft.Excel.plist, the next time I start up Excel, the same 10 files are there. <br><br>If I open several new files, they initially get added to the list. However, once again, once I re-start Excel, the original 10 files are still there. <br><br>If I turn off the recently opened list in the Excel General preferences, delete ...

Formula
Have a simple idea that is giving me fits. Have a worksheet with numberic data and labels in rows and columns. Below each column I use a simple SUM() function which works fine, until I disturb a row of data. By disturb, what I mean is that if I move the top row of data down to the bottom of the array, highlight the entire data area, move that area up one row, I lose the correct references in the SUM() function. I'm just moving the data area, not the row containing my SUM() functions - that stays put. IOW, the SUM() function is in row 12, the data area extends from row 1 through ...

Excel page format changes
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hello, <br><br>My client creates spreadsheets for her boss. She pushes the boundaries to get things to fit on the screen. <br> Uses A4, margins at 1.5cms and scales to 90%. <br><br>When her boss receives it by email the margins go to 2.5cms, scales to 100%. They have the same versions 12.2.4 and Leopard. They are just about to be upgraded to Snow Leopard. Any idea why this could be happening? Thanks. ...

Display only duplicate values and delete UNIQUE Items
All I have a very large list of data and on a monthly basis i need to display only the duplicate items in a spreadsheet. I would like to do this in VBA and then run it as a macro on the spreadsheet. Alot of the sites that i have seen only show how to removed the duplicates. Excel 2007 has a function which removed all duplicates but so far i have found nothing that only displays the duplciates.... any ideas anyone? Assuming that the field you use to determine uniqueness is column A, you can put this formula in a helper column: =3DIF(COUNTIF(A:A,A2)>1,"Duplicate","Unique&...

Outlook 2003 changing global auto archive settings?
Outlook offers to Run Auto Archive every days. It allows you to run it more frequently, but I would like to run it less frequently. In other words, I would like to run it every 120 days and then archive the bottom four months. It doesn't seem that you can change the auto archive setting above 60 days. Is there a way to do this? I have the box checked to ask me before auto archiving - but if I say no, will it continue to ask? Or, will it never ask again? Essentially I would like to archive every 4 months and not more frequently - and I would like to archive my inbox and my se...

How can I change dollar sybbol to euro symbol?
If data is numeric then use Format Cells and choose a format with a Euro symbol, or if text just do an Edit / Replace. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------�------------------------------�---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------�------------------------------�---------------- "zwerver" <zwerver@discussions.microsoft.com> wrote in message news:1821DD41-E29A-4034-9A2E-9DCB5AB6B4BD@micros...

Wrong message displayed
If I move messages to another folder (archive), then go back to read the message, it shows one message, no matter which one I select. It doesn't display any of the other messages. Where might this 'archive' folder be located ? 1. Storage folders 2. Email account If 2, is this a pop3, IMAP, or Hotmail(Http) account ? -- ...winston ms-mvp mail "Marlon Medina" <MarlonMedina@discussions.microsoft.com> wrote in message news:93E6F96B-3619-4491-ADEA-DC84385C3374@microsoft.com... > If I move messages to another folder (archive), then go bac...

Charts and "global" changes
Hello all, I'm actually working in Microsoft Access97, which uses MSGraph 2000 for charting, so I thought this was really a "charting" question... not an Access question. I have an XY scatter chart (using just points... no X or Y error bars or lines) all laid out just fine, but I have a couple of questions that would make my work a lot easier. When the data first displayed on the chart, every data point had a different "style"... circles, triangles, x's, etc... I had to go into every one individually and change them to a black dot at size 3. Isn...

increment a value by 1
How do I increment, automatically, a cell by 1 if a condition is met? If one of my cells reaches 10k I want another cell to increment by 1. Can anyone help me if this function can be done? Thank you, Ron Do you want the increment only when it changes from below 10K to above 10K, or anytime is is above 10K? -- Gary's Student "Workshops" wrote: > How do I increment, automatically, a cell by 1 if a condition is met? If one > of my cells reaches 10k I want another cell to increment by 1. Can anyone > help me if this function can be done? > Thank you, Ron Increm...

Exclude like values with a JOIN?
I have the following query: SELECT dbo.qry_UV_CARS_PlateX_ShouldBeValued.Yl_iD FROM dbo.qry_UV_CARS_PlateX_Valued RIGHT OUTER JOIN dbo.qry_UV_CARS_PlateX_ShouldBeValued ON dbo.qry_UV_CARS_PlateX_Valued.Yl_iD = dbo.qry_UV_CARS_PlateX_ShouldBeValued.Yl_iD WHERE (dbo.qry_UV_CARS_PlateX_Valued.Yl_iD IS NULL) And I have another secondary table containing the same field as Yl_iD. What I want to do is have my main query exclude all the Yl_iD values that are in my secondary table. What JOIN and criteria will do this? Many thanks.....Jason WHERE Yl_iD N...

Managed Newsgroups Test
Back on 9/20, I posted the following under the topic'Global Account Adjustments & Local Credits': Question An ex-employee came in today to make a payment on account. He paid $100. When the payment was tendered, the 'Apply Local Credit' option was selected (by default). We got a two-page receipt that appears to have applied all of the Gloabal Account Adjustments that were made as part of the payroll deduction plan we have for employee purchases. To confirm, I pulled up my personal account and made a $0 payment. The 'Apply Local Credit' feature churned out another ...