Creating Consolidated Spreadsheet...

[jhicker]

I have two seperate worksheets with data from two companies. Example:

(Worksheet 1)

COMPANY A
McDonalds Account
Burger King Account
Wal Mart Account

(Worksheet 2)

COMPANY B
Outback Account
Home Depot Account

I would like to create a third worksheet that will consolidate the
entries for the two companies. I would want it to look like:

BOTH COMPANIES
McDonalds Account
Burger King Account
Wal Mart Account
Outback Account
Home Depot Account

The problem is that the number of entries in each company will change
from month to month. But I would like to create a formula of some sort
that will list all entries in the first sheet and then pick up showing
the entries in the second sheet in one consolidated list. I've been
going this route:

=IF('SHEET1'!A2<>"",'SHEET1'!A2,'SHEET2'!$A$2)

This gets all the first list and the first entry of the second list but
I can't modify the formula to add entries in order after the first entry
on the second sheet. I could manually modify the formula to display the
data right this month but when the variable change next month it will
need to be modified again (I could copy paste alot faster if I was
going to do this).

Is there anyway to do this without getting into VBA code?

Thanks!
Jon

 

[Tom Ogilvy]

In A1 of Sheet3 enter:

=IF(ROW()<=COUNTA(Sheet1!A:A),Sheet1!A1,INDIRECT(ADDRESS(ROW()-COUNTA(Sheet1
!A:A),1,1,TRUE,"Sheet2")))

in B1

=IF(ROW()<=COUNTA(Sheet1!A:A),Sheet1!B1,INDIRECT(ADDRESS(ROW()-COUNTA(Sheet1
!A:A),2,1,TRUE,"Sheet2")))

Then drag fill down the column until you run out of data.

 

[Guest]

You might want to use MS Query to consolidate the data from your
two worksheets:

This example uses 2 named ranges in 2 different sheets of the same workbook.

Assumptions:
The data in each worksheet is structured like a table:
--->Col headings (Dept, PartNum, Desc, Price)
--->Columns are in the same order.

The data in each sheet must be in named ranges.
--->I used rngCompAData for Company A's data and rngCompBData for Company
B's data

Save the workbook before proceeding:

(Note: MS Query may display warnings about it's ability to show the query
....ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)Data>Import External Data>New Database Query

Databases: Excel Files

Browse to the file, pick the data range to import.
--->Accept defaults until the next step.

At The last screen select the View data/Edit the Query option.

Click the [SQL] button

Replace the displayed SQL code with an adapted version of this:

SELECT * FROM `C:\MyWorkbook`.rngCompAData
UNION ALL
SELECT * FROM `C:\MyWorkbook`.rngCompBData

(Note: the apostrophes in the SQL code ( ` )are located on the same key as
the tilde (~) )

Return the data to Excel.

Once that is done....to get the latest data just click in the data range
then Data>Refresh Data.
(You can edit the query at any time to add/remove data sources and/or fields.)


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[jhicker]

I think this will work for me. I need to tweak it as the charts I'
using are actually a little more complicated but I think I can take i
from here.

Thanks for your help!

Jo