Combining Worksheets and keeping them separate

  • Thread starter Thread starter Glenn
  • Start date Start date
G

Glenn

I have a workbook that contains two worksheets.

Worksheet one as information such as name, address, etc.

Worksheet two has the same fields/columns of information, however the data
itself is different.

I need to keep these two lists separated, however, I also need to have them
combined at times.

Is it possible to create "Worksheet 3" that will automatically update when I
add/modify records in Worksheet 1 or Worksheet 2?

Thanks.
 
Glenn,

There is not an easy way to do that.

Instead of three worksheets, use one worksheet (a single database). Then
you have two choices.

1) Enter all your data for the two sheets as one record, using extra fields
as needed (but only two identifier columns). Then, hide the columns with
the fields that would normally be on sheet1 when you only want to see what
is normally on sheet2, and hide the columns with the fields that would
normally be on sheet2 when you only want to see what is normally on sheet1.
And unhide all the columns when you want to see everything or when you need
to add or change records.

2) Enter all your data for the two sheets as two records, and use one extra
field, showing which sheet (or similar identifier) the data belongs on.
Then use Data filters on that extra field to show/hide your data as
appropriate.

HTH,
Bernie
MS Excel MVP
 
This can achieved using a query
Create range for data in worksheet 1 as Table1 (Add extra empy rows for
future data addition)
Create range for data in worksheet 2 as Table2 (Add extra empy rows for
future data addition)
Save work book (Assume the name of work book as "testbook")
Insert external data in worksheet 3 (cell a1) as given below
Data->Import External Data->New Database Query->ExcelFiles* (In Database tab
of Choose data source dialog box)->
"testbook" (the name of the work book u r
working)->Table1->">"->next->next->next->"View or edit
query"->ok->SQL->[paste the following]

SELECT Table1.Field1, Table1.Field2, Table1.Field3 FROM `test book`.table1
table1 WHERE table1.field1<>''
UNION
SELECT Table2.Field1, Table2.Field2, Table2.Field3 FROM `test book`.table2
table2 WHERE table2.field1<>''
Close all the box and save query in a name u like
(It may give some message that it can not show graphically etc- forget that)
Return the data to excel file

Now the combined data will be in yr worksheet 3
Hope this will solve yr problem
Table1.field1, Table1.Field2, Table1.Field3 FROM `test book`.table1 table1
WHERE table1.field1
Click on the data in worksheet 3->right click->refresh data - to get the
latest combined data
Play with the query the way u want
Regards
Athiappan
 
Back
Top