software

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'!A2lt;gt;quot;quot;,'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--
jhicker
------------------------------------------------------------------------
jhicker's Profile: www.excelforum.com/member.php...oamp;userid=31736
View this thread: www.excelforum.com/showthread...hreadid=514516In A1 of Sheet3 enter:

=IF(ROW()lt;=COUNTA(Sheet1!A:A),Sheet1!A1,INDIRECT(A DDRESS(ROW()-COUNTA(Sheet1
!A:A),1,1,TRUE,quot;Sheet2quot;)))

in B1

=IF(ROW()lt;=COUNTA(Sheet1!A:A),Sheet1!B1,INDIRECT(A DDRESS(ROW()-COUNTA(Sheet1
!A:A),2,1,TRUE,quot;Sheet2quot;)))

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

--
Regards,
Tom Ogilvyquot;jhickerquot; gt; wrote in
message ...
gt;
gt; I have two seperate worksheets with data from two companies. Example:
gt;
gt; (Worksheet 1)
gt;
gt; COMPANY A
gt; McDonalds Account
gt; Burger King Account
gt; Wal Mart Account
gt;
gt; (Worksheet 2)
gt;
gt; COMPANY B
gt; Outback Account
gt; Home Depot Account
gt;
gt; I would like to create a third worksheet that will consolidate the
gt; entries for the two companies. I would want it to look like:
gt;
gt; BOTH COMPANIES
gt; McDonalds Account
gt; Burger King Account
gt; Wal Mart Account
gt; Outback Account
gt; Home Depot Account
gt;
gt; The problem is that the number of entries in each company will change
gt; from month to month. But I would like to create a formula of some sort
gt; that will list all entries in the first sheet and then pick up showing
gt; the entries in the second sheet in one consolidated list. I've been
gt; going this route:
gt;
gt; =IF('SHEET1'!A2lt;gt;quot;quot;,'SHEET1'!A2,'SHEET2'!$A$2)
gt;
gt; This gets all the first list and the first entry of the second list but
gt; I can't modify the formula to add entries in order after the first entry
gt; on the second sheet. I could manually modify the formula to display the
gt; data right this month but when the variable change next month it will
gt; need to be modified again (I could copy paste alot faster if I was
gt; going to do this).
gt;
gt; Is there anyway to do this without getting into VBA code?
gt;
gt; Thanks!
gt; Jon
gt;
gt;
gt; --
gt; jhicker
gt; ------------------------------------------------------------------------
gt; jhicker's Profile:
www.excelforum.com/member.php...oamp;userid=31736
gt; View this thread: www.excelforum.com/showthread...hreadid=514516
gt;
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:
---gt;Col headings (Dept, PartNum, Desc, Price)
---gt;Columns are in the same order.

The data in each sheet must be in named ranges.
---gt;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)Datagt;Import External Datagt;New Database Query
gt;Databases: Excel Files

Browse to the file, pick the data range to import.
---gt;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 Datagt;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-Proquot;jhickerquot; wrote:

gt;
gt; I have two seperate worksheets with data from two companies. Example:
gt;
gt; (Worksheet 1)
gt;
gt; COMPANY A
gt; McDonalds Account
gt; Burger King Account
gt; Wal Mart Account
gt;
gt; (Worksheet 2)
gt;
gt; COMPANY B
gt; Outback Account
gt; Home Depot Account
gt;
gt; I would like to create a third worksheet that will consolidate the
gt; entries for the two companies. I would want it to look like:
gt;
gt; BOTH COMPANIES
gt; McDonalds Account
gt; Burger King Account
gt; Wal Mart Account
gt; Outback Account
gt; Home Depot Account
gt;
gt; The problem is that the number of entries in each company will change
gt; from month to month. But I would like to create a formula of some sort
gt; that will list all entries in the first sheet and then pick up showing
gt; the entries in the second sheet in one consolidated list. I've been
gt; going this route:
gt;
gt; =IF('SHEET1'!A2lt;gt;quot;quot;,'SHEET1'!A2,'SHEET2'!$A$2)
gt;
gt; This gets all the first list and the first entry of the second list but
gt; I can't modify the formula to add entries in order after the first entry
gt; on the second sheet. I could manually modify the formula to display the
gt; data right this month but when the variable change next month it will
gt; need to be modified again (I could copy paste alot faster if I was
gt; going to do this).
gt;
gt; Is there anyway to do this without getting into VBA code?
gt;
gt; Thanks!
gt; Jon
gt;
gt;
gt; --
gt; jhicker
gt; ------------------------------------------------------------------------
gt; jhicker's Profile: www.excelforum.com/member.php...oamp;userid=31736
gt; View this thread: www.excelforum.com/showthread...hreadid=514516
gt;
gt;


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

Thanks for your help!

Jon--
jhicker
------------------------------------------------------------------------
jhicker's Profile: www.excelforum.com/member.php...oamp;userid=31736
View this thread: www.excelforum.com/showthread...hreadid=514516