Collect data from spesific cell in multiple sheets

How can I collect data from cell A1 on sheet 01.01 thru 31.12 (Dates) to 
summary sheet? 

I've tried fill and copy formula with different use of ! and $ but I've not 
been able to solve this by my self.

Thank you
3/11/2010 10:29:01 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies

Similar Articles

[PageSpeed] 58


The correct syntax is


Substitute sheet1 & sheet3 for the first/last sheets in the range you want 
to sum

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 

"Espen Rostad" wrote:

> How can I collect data from cell A1 on sheet 01.01 thru 31.12 (Dates) to 
> summary sheet? 
> I've tried fill and copy formula with different use of ! and $ but I've not 
> been able to solve this by my self.
> Thank you
3/11/2010 10:53:01 AM
Thank you Mike, but I need all results in a row. Eg Sheet '1.1' cell A1 in 
Sheet 'SUM' A1, Sheet '2.1' cell A1 in Sheet 'SUM' A2 .. and so on

Mike H skrev:
3/11/2010 11:03:01 AM
hi Espen

On your Sum sheet, enter the list of your sheet names 01.01 to 31.12 in 
cells A1:A365
In B1 enter
=INDIRECT("'" & A1 &"'!A1")
and copy down

Roger Govier

Espen Rostad wrote:
> Thank you Mike, but I need all results in a row. Eg Sheet '1.1' cell A1 in 
> Sheet 'SUM' A1, Sheet '2.1' cell A1 in Sheet 'SUM' A2 .. and so on
> Mike H skrev:
3/11/2010 12:11:47 PM
Thank you Roger - that did it

Roger Govier skrev:

3/16/2010 9:20:01 AM

Similar Artilces:

3 Different Data Sets
I am trying to create one real estate chart that has the following three components: (1) Bar Chart = Square Footage, (2) Line chart representing a percentage, and (3) Line chart representing dollars. Any idea if this can even be done? Thanks. ...

Migrating Data From ACT to CRM Using DMF?
Hi, I was wondering if anyone has successfully migrated data from an ACT v6 database, to CRM 3? If so, could they supply some steps, or information on how they did it, plus if they ran into any problems! I've had a quick look at the DMF xls spreadsheets, and the wizards, which don't seem to complicated, however its importing the data from ACT to CDF using the Microsoft SQL Server Data Transformation Services (DTS) to populate the CDF that looks a bit complex. I think I read somewhere that you have to write some coding in sql to do this, correct? Also I've read some posts t...

Multiple Condition Formatting
Hi, Can anyone help me on this: =IF(AND(F65<>0,G59="SELECT CUSTOMER"),APPLY FORMATTING, DON'T APPLY FORMATTING) I have been exploring the Conditional Formatting in 2007 and I may be wrong but I don't see a way to set a condition whereby two(2) logical conditions must be met for it to apply the formatting. Any ideas?? This could easily be applied to other conditions. Mike "Gerard Sanchez" wrote: > Hi, > > Can anyone help me on this: > ...

Using cell reference with logical operator in DGET expression
I am using DGET to search an array in the worksheet to find a particular percent to use in a calculation elsewhere in the spreadsheet. The row members of the array contain a series of from and to values that I use to identify which row has the percent I am looking for. I have defined the array as a range and I can use the logical operatirs with numeric values in the range criteria and everything works fine. For example, I can use <500 in the appropriate cell in range criteria to find the percent to use when the value for that column in the array is less than 500. All working f...

reference cell above even if row deleted
I would like to be able to reference a cell imediately above the current cell even if the row above that cell had just been deleted. For instance: contents of A5: =A4+$B$1 then delete row 4 and rather than have contents of A4:#REF!+$B$1 have it A4:A3+$B$1 Is there some way to do this? Thanks for any tips. John Keith You can use the OFFSET function: =OFFSET(A5,-1,0)+$B$1 John Keith wrote: > I would like to be able to reference a cell imediately above the > current cell even if the row above that cell had just been deleted. > > For instance: > > conte...

delete data between subtotals
I would like to delete all data between my subtotals without the subtotals changing. I tried to collapse the whole sheet and to copy the then visible data (with paste values only). this did not work. is there any other way to get rid of data between subtotals? appreciate your help. please see attachement. Attachment filename: illustration_1.xls Download attachment: --- Message posted from Hi see Peo's response in your original thread -- Regards Frank Kabel Frankfurt, Germany >...

Create a list with multiple criteria
I have a data base with 1,000's of addresses w/zipcodes (Sheet 1 columns A to E, E being zipcodes). After setting a base address I get all of the zipcodes within a given radius (in this case 70 zipcodes), listed in Sheet 2, Column A. Next, I use COUNTIF (in column B) to find how many addresses are in each zipcode, in this example there are 46 addresses within the 70 zipcodes. What I want to do is create a list in sheet three that will list each with the data from Sheet 1 columns A to E). Any help would be appreciated. Ronbo ...

SumIFS Cell
I am trying to reference a cell in a formula and I would like to say anything greater then cell J2 but when I input this it searches for text. =SUMIFS($B$2:$B$27,$A$2:$A$27,"000001",$C$2:$C$27,>D2) A B C D 1 00001 100 40248 40248 2 12001 150 40237 3 15001 200 40237 4 00001 150 40290 5 00001 50 40350 I would like the total to return 200 because Cell C5 and C4 are larger than Cell D1 in respect to the Sku number I would like to sum. But when I put in >J2 it enters “>J2” and it won’t return a value other then 0. (If I put in just D2 then it returns a valu...

Combining multiple Stacked Charts
I have 3 scenarios with info on Asset A and Asset B for 10 years. I can create stack charts for each scenario independently, but can not figure out how to combine all 3 onto 1 chart across multiple years. x axis is year and each year should have 3 stacked lines Y Axis should be total value of both Assets Any help appreciated Does this help? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - _______ "JANeyer" <...

multiple colors on format of plot area
How can I change the format of the plot area to multiple colors. for example I would like to have a Bar Chart with the background plot area starting @ blue , then Green, then Red. Then have the temperature bars plot over this area. thanks You can do so by bringing a stacked column chart into the mix. Please see Jon's example below: -- John Mansfield "BillO" wrote: > How can I change the format of the plot area to multiple colors. > > for example I would like to have a ...

Table Cell
I have created a form using a table. I want to lock or block cells that should not be changed. How do you do this? ...

combine multiple excel file in to one excel file and multiple worksheet
I am wanting to use the following code to combine worksheets from multiple files. However I would like to be able to select folder which contains files in a more automated way that having to change the code every time, and also copy all worksheets with links and formulas removed. Any help on this is greatly appreciated as I have limited code knowledge. Sub Copy_them() > Dim TargetWkbk As Workbook > Dim mrgWkbk As Workbook > Dim i As Long > Dim Wks As Worksheet > Dim fName As String > Application.ScreenUpdating = False > Set TargetWkbk ...

multiple text edit
Hi all, I'm a beginner when it comes to Visio. I’m using Visio 2007 and I’m wondering if the following is possible. What I want to be able to do is to change the text in a number of textboxes by just editing one of them - that is, when I change the text in one textbox, the three other textboxes will display what I’ve just written automatically. Can this be done? Thanks in advance. the answer is "kinda". You can coordinate text between shapes so that changing a specific shape will be mirrored to others. It's a one to many, you don't get the option of changing anyo...

Round to a Multiple
In an Access Report how do i round to the nearest multiple? ex. 45263 Round to 45260 45266 Round to 45270 Thanks, On Fri, 18 Jan 2008 16:40:14 -0600, "mh" <> wrote: >In an Access Report how do i round to the nearest multiple? > >ex. 45263 Round to 45260 > 45266 Round to 45270 > >Thanks, Multiple of 10? 10*Round([field]/10,0) John W. Vinson [MVP] There's a bunch of rounding info here: -- Allen Browne - Microsoft MVP. Perth, Western A...

Cell Protection #8
Hello: I am using the UserInterfaceOnly to protect my worksheet. I have it placed in the workbook object. Unfortunately, after the workbook is open the user can Tools>>Protection>>Unprotect Sheet. Is there a way to password out this option? I would like the user to have access to only the standard toolbar, is there a method to stop them from enabling various toolbars, menus options and manipulating the sheet, etc. Kind regards, D.Parker Wouldn't it be simpler to use a password that the user doesn't know? And don't forget to protect your project. In th...

External data query using MAX/SUM
I'm trying to create an external data link using Microsoft Query. Ho do I write SQL in Microsoft query that will return a max date and su an amount field for the max date only? Current SQL is as follows: SELECT DISTINCT CB_PORTFOLIO_DMN.PORTFOLIO_NAME Max(CB_POOL_PORTFOLIO.POST_DATE), Sum(CB_POOL_PORTFOLIO.EXPOSURE_AMT) Count(CB_POOL_PORTFOLIO.NBR_AVG_MATY_YRS) FROM CB06U.CB_POOL_PORTFOLIO CB_POOL_PORTFOLIO, CB06U.CB_PORTFOLIO_DM CB_PORTFOLIO_DMN WHERE CB_PORTFOLIO_DMN.PORTFOLIO_ID = CB_POOL_PORTFOLIO.PORTFOLIO_ID GROUP BY CB_PORTFOLIO_DMN.PORTFOLIO_NAME This returns the sum of the exp...

jumping data
hi i have a form which shows customer details, and this has a subform where you can enter the date and notes about the customer. the subform is in a datasheet view. so everytime you want to add some notes about the customer, you enter the date and write the notes. there are many customers on the database, and we make quite a few notes for each customer. Now when i go into the customer and put in a note, then come out and go back into it, the data has moved up a few columns and goes into the wrong place, rather than stay in the same place where it was entered. also when...

how to insert chart for imported data in a report?
plz reply soon if you know Please use the large white space under the subject to enter a detailed question. It also helps to read some past threads that might pertain to your question (whatever it is). -- Duane Hookom Microsoft Access MVP "varsha from pilibhit" wrote: > plz reply soon if you know ...

Outlook client for CRM V3
We have a client who has users who want to make changes to the 'my contacts' local data group, so they sync their Outlook contacts with CRM. They all use the desktop client. But the save button is greyed out. They have a CRM role assigned with the Outlook sync permission and they have local admin rights. I've also checked they have write permissions to the C:\Documents and Settings\User.Name\Application Data\Microsoft\MSCRM folder and it's subfolders. What else could be disabling the save option? Becuase My Contacts is a default group, I don't think you can make c...

How do you move the curser around in a cell
What do mean by "cursor" and what do you want to do? HTH Otto "krisf" <> wrote in message > ...

Setting up multiple accounts
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop Hi guys, <br><br>Just switched over to the mac world and am now setting up my email. <br><br>I have 2 separate email address all hosted on the same mail server - <br><br>I added my default email address which works fine in Entourage but I continue to get the following error message with my second email account: <br><br>Mail could not be received at this time. <br><br>The server for &quot;Cheekyage's&quot; r...

Multiple emails for one contact in distribution list
I have created a group to send out bi-monthly emails from my personal email account. One of the people in the group would like to have the email sent to her work email and to her personal email. I have attempted to add both email addresses to the group, and initially it does. After syncing, however, the personal email disappears from the group list because it is not the primary email listed in the contact. Is there a way to include both email addresses without creating a separate contact for just the personal email? I am running Outlook 2010 beta. My email is through Hotmai...

list sheet comments
Hi, I have a sheet with a lot of comments. I would like to create a macro that lists all the comments of the sheet in column F. I=B4ve tried something similar like this: For Each cmt in ActiveSheet.Comments MsgBox cmt.Text Next cmt But I don=B4t know how to list each comment in each cell of column F instead of a msgbox. thanks Dim StartCell as Range dim Cmt as comment with activesheet set startcell = .range("F1") for each cmt in .comments startcell.value = cmt.txt 'come down to the next cell set startcell = startcell.offset(1,0) n...

How to add 6% to entire sheet?
I have a spreadsheet and I want to add 6% to whatever value is already in each cell. How does a guy do this? --- Message posted from Hi "skeetley" Make sure that you save a backup. To my mind it's probably best to copy your table of data to a new sheet. Put 1.06 in a cell Select that cell Copy Select the data to be uplifted Paste Special > Multiply > OK -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "s...

Moving Data Automaticaly
Can you help I have a table (part shown below) the Date and Day moves automaticaly to left at midnight - is it possible to get the rest of the data to move as well 25-Nov 26-Nov 27-Nov 28-Nov Sun Mon Tue Wed 07:00 07:00 TBA on off on off on off on off 1 1 1 1 1 1 1 1 1 1 1 1 1 1 -- JohnM Please explain meaning of "date and day MOVES to left" Are you using conditionally formatting to highlight the cell with the current date? best wishes -- Bernard V Liengme Microsoft Excel MVP