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
|
|
0
|
|
|
|
Reply
|
 Utf
|
12/17/2009 10:05:01 PM |
|
Wow, sounds like a tall order. Let’s start with one thing and then take care
of the other things. Go here:
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
we’ll take care of the other stuff.
Keep in mind, this probably won’t be really easy. You’re trying to make a
spreadsheet work like a database. You can certainly do it, but it’s 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
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/17/2009 11:26:01 PM
|
|
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
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/17/2009 11:29:01 PM
|
|
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,ROW()-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,ROW()-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
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/18/2009 12:10:01 AM
|
|
Excel 2007 PivotTable
Combine multiple sheets.
Group by months, subtotals, totals, filter, graph.
All with no formulas, no code.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/12_26_09.xlsx
|
|
|
0
|
|
|
|
Reply
|
Herbert
|
12/26/2009 4:45:01 PM
|
|
|
4 Replies
1117 Views
(page loaded in 0.166 seconds)
Similiar Articles: combining data from multiple worksheets into one - microsoft ...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 li... Move data from multiple tabs into one worksheet - microsoft.public ...Move data from multiple tabs into one worksheet - microsoft.public ... combining data from multiple worksheets into one - microsoft ... combine/merge 2 worksheet (tabs ... Merge multiple worksheets into one - microsoft.public.excel ...combining data from multiple worksheets into one - microsoft ... Merge Multiple Worksheets and Workbooks into One Worksheet ... combining data from multiple worksheets ... Merge Multiple Worksheets and Workbooks into One Worksheet ...combining data from multiple worksheets into one - microsoft ... Merge Multiple Worksheets and Workbooks into One Worksheet ... combining data from multiple worksheets ... combine multiple spreadsheets into one workbook - microsoft.public ...Excel Macro VBA - Combine Multiple Workbooks into One - Free Excel ... Free Excel Macro - A free microsoft excel macro that will combine data from multiple worksheets and ... combining data from two columns into one - microsoft.public.excel ...Re: How to merge 2 columns into one ... ... let multiple users ... How to sort data from one worksheet into other worksheets ... How do I merge 2 worksheets into one ... Excel: Combine contents from identical cells on multiple sheets ...Excel Macro VBA - Combine Multiple Workbooks into One - Free Excel ..... Excel Macro - A free microsoft excel macro that will combine data from multiple worksheets ... Combining two excel sheets into one, where one sheet has multiple ...Combine two data sources into one pivot table in Excel ... into 1 work sheet - microsoft ... Combine two data sources into one ... Merge multiple worksheets into ... combine/merge 2 worksheet (tabs) into 1 work sheet - microsoft ...combining data from multiple worksheets into one - microsoft ... combine/merge 2 worksheet (tabs) into 1 work sheet - microsoft ... combining data from multiple worksheets ... Merging data from 2 workbooks - microsoft.public.mac.office.excel ...combining data from multiple worksheets into one - microsoft ... Merging data from 2 workbooks - microsoft.public.mac.office.excel ... Merge Multiple Worksheets and ... Merging Data from Multiple Workbooks into a Summary Workbook in Excel... number of samples and a handy add-in to merge data from multiple workbooks located in one folder into a ... Consolidating Data from Multiple Worksheets into a Summary ... Combining data from multiple worksheets into one ExcelI have 4 files with hundreds of worksheets in each file. I need to combine all 700,000 rows of data from all the worksheets in these 4 files into one work Condensing Multiple Worksheets Into One (Microsoft Excel)If you get workbooks that have identically structured data on each worksheet, you may be interested in a way to combine the multiple worksheets into a single, large ... Excel: Combining data from multiple workbooks into one worksheet ...My problem is about combining data range from multiple workbooks, into one workbook, so that ... worksheet named "Date", in which the data is entered. The remaining sheets ... Answer : Combining data from multiple worksheets into oneCombining data from multiple worksheets into one - answer - I have 4 files with hundreds of worksheets in each file. I need to combine all 700,000 rows of data from ... 7/19/2012 6:36:33 PM
|
Search |
Post Question |
Groups |
Stream |
About |
Register
39 followers. 