Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combining data from multiple worksheets into one
I have several worksheets in one workbook. Each worksheet is for a different
store location (i.e. Store 1, Store 2, Store 3, etc). I have dates listed in Column A and profits listed in Column B. I would like to create a another worksheet that combines entries from all these stores into one master log but listed in sequential dates. I want this master log to update automatically every time I enter an entry into one of the individual stores. I don't want to just copy and paste old data into the master log. I also want column C to state which store this came from. Is there some sort of lookup function that can do this for me? Basically, if I enter into sheet Store 1: 1/1/09 $100 1/5/09 $200 And enter into sheet store 2: 1/1/09 $200 1/3/09 $400 I want the master log sheet to automatically fill as: 1/1/09 $100 Store 1 1/1/09 $200 Store 2 1/3/09 $400 Store 2 1/5/09 $200 Store 2 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combining data from multiple worksheets into one
Wow, sounds like a tall order. Lets start with one thing and then take care
of the other things. Go he http://www.rondebruin.nl/copy2.htm Read it carefully and copy/paste the code into a module so you can consolidate all your data onto one SummarySheet. Get that working and then well take care of the other stuff. Keep in mind, this probably wont be really easy. Youre trying to make a spreadsheet work like a database. You can certainly do it, but its not good practice. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "yowzers" wrote: I have several worksheets in one workbook. Each worksheet is for a different store location (i.e. Store 1, Store 2, Store 3, etc). I have dates listed in Column A and profits listed in Column B. I would like to create a another worksheet that combines entries from all these stores into one master log but listed in sequential dates. I want this master log to update automatically every time I enter an entry into one of the individual stores. I don't want to just copy and paste old data into the master log. I also want column C to state which store this came from. Is there some sort of lookup function that can do this for me? Basically, if I enter into sheet Store 1: 1/1/09 $100 1/5/09 $200 And enter into sheet store 2: 1/1/09 $200 1/3/09 $400 I want the master log sheet to automatically fill as: 1/1/09 $100 Store 1 1/1/09 $200 Store 2 1/3/09 $400 Store 2 1/5/09 $200 Store 2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combining data from multiple worksheets into one
Hummm, I just found this code; little easier to work with perhaps:
Public Sub Test() Dim sh As Worksheet Dim newSh As Worksheet Dim i As Long Dim NextRow As Long NextRow = 1 Set newSh = Worksheets.Add(after:=Worksheets(Worksheets.Count) ) newSh.Name = "FINAL" For Each sh In Worksheets If sh.Name < "FINAL" Then sh.UsedRange.Copy newSh.Cells(NextRow, "A") With newSh.UsedRange NextRow = .Row + .Rows.Count + 1 End With End If Next sh End Sub -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "yowzers" wrote: I have several worksheets in one workbook. Each worksheet is for a different store location (i.e. Store 1, Store 2, Store 3, etc). I have dates listed in Column A and profits listed in Column B. I would like to create a another worksheet that combines entries from all these stores into one master log but listed in sequential dates. I want this master log to update automatically every time I enter an entry into one of the individual stores. I don't want to just copy and paste old data into the master log. I also want column C to state which store this came from. Is there some sort of lookup function that can do this for me? Basically, if I enter into sheet Store 1: 1/1/09 $100 1/5/09 $200 And enter into sheet store 2: 1/1/09 $200 1/3/09 $400 I want the master log sheet to automatically fill as: 1/1/09 $100 Store 1 1/1/09 $200 Store 2 1/3/09 $400 Store 2 1/5/09 $200 Store 2 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combining data from multiple worksheets into one
There is the formula that will combine it to you
Copy this in col A =IF(ROW()COUNTA(Sheet1!A:A)+COUNTA(Sheet2!A:A),"" ,IF(ROW()<=COUNTA(Sheet1!A:A),OFFSET(Sheet1!$A$1,R OW()-1,0),OFFSET(Sheet2!$A$1,ROW()-COUNTA(Sheet1!A:A)-1,0))) Copy this to column B =IF(ROW()COUNTA(Sheet1!B:B)+COUNTA(Sheet2!B:B),"" ,IF(ROW()<=COUNTA(Sheet1!B:B),OFFSET(Sheet1!$B$1,R OW()-1,0),OFFSET(Sheet2!$B$1,ROW()-COUNTA(Sheet1!B:B)-1,0))) Click yes if helped -- Greatly appreciated Eva "yowzers" wrote: I have several worksheets in one workbook. Each worksheet is for a different store location (i.e. Store 1, Store 2, Store 3, etc). I have dates listed in Column A and profits listed in Column B. I would like to create a another worksheet that combines entries from all these stores into one master log but listed in sequential dates. I want this master log to update automatically every time I enter an entry into one of the individual stores. I don't want to just copy and paste old data into the master log. I also want column C to state which store this came from. Is there some sort of lookup function that can do this for me? Basically, if I enter into sheet Store 1: 1/1/09 $100 1/5/09 $200 And enter into sheet store 2: 1/1/09 $200 1/3/09 $400 I want the master log sheet to automatically fill as: 1/1/09 $100 Store 1 1/1/09 $200 Store 2 1/3/09 $400 Store 2 1/5/09 $200 Store 2 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combining data from multiple worksheets into one
Excel 2007 PivotTable
Combine multiple sheets. Group by months, subtotals, totals, filter, graph. All with no formulas, no code. http://c0444202.cdn.cloudfiles.racks.../12_26_09.xlsx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combining data multiple worksheets into one? | Excel Discussion (Misc queries) | |||
Combining data from multiple worksheets into master worksheet | Excel Worksheet Functions | |||
Combining data from multiple worksheets. | Excel Discussion (Misc queries) | |||
Combining Multiple Worksheets | Excel Discussion (Misc queries) | |||
Combining data from multiple worksheets and separate spreadsheets | Excel Discussion (Misc queries) |