Combine two worksheets in the same workbook to a third worksheet

  • Thread starter Thread starter meshsa
  • Start date Start date
M

meshsa

Hello,

I have two worksheets (Books French, Books Spanish) in the same
workbook each with the same data categories. (Author, Title, Year,
Publisher, Category, Remarks)

How can I combine the data sets from each worksheet in a single
worksheet, so that changes in the individual worksheets are reflected
in the new worksheet with the combined data.

Have tried vlookup but it turns into a dogs dinner for me, would
appreciate any help.

Cheers,

Mesh
 
Mesh,

You want the combined worksheet to continuously reflect the contents of the individual
worksheets. That needs clarification. Do you want all the Books French rows to appear,
followed immediately by the Books Spanish? Ading a Books French row will cause all of the
Books Spanish to need to move down. You can see that it's going to get messy.

VLOOKUP looks for one row matching a criteria. Not for you.

Take a look at "Data across multiple sheets" at
http://www.smokeylake.com/excel/excel_truths.htm. In your case, the additional column will
be for French/Spanish. This might get you into a good direction. Don't be took quick to
dismiss it -- the urge to keep data in separate sheets is sometimes strong.
--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
Earl,

Many thanks for the ideas, I took onboard what you said and added an
extra column in each of the French and Spanish worksheets, basically a
language identifier.

I don't mind how that data appears in the consolidated sheets as I can
easily apply an autofilter to define the characteristics I want when I
do a search. (Both worksheets are primarily for Spanish and French Lit
reading lists for a European Studies course I teach)

As the individual language sheets are dynamic, I would like for them
to be reflected in a consolidated sheet without the need to cut/
paste .

Hope this clarifies things,

Cheers,

N
 
Mesh,

The idea is to not have the individual sheets at all. Combine them, as described in the
article, and have an extra column to specify French or Spanish. Then use Autofilter to
reduce the consolidated sheet to one or the other as needed.

--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------
 
Back
Top