Connect different worksheets

  • Thread starter Thread starter Marten
  • Start date Start date
M

Marten

Hi all,

I would like to do the following:

I have two worksheets. In one of the worksheets I have a list of items, lets
say
ID VEH. COL. PRIZE.
1 CAR BLUE 900
2 CAR RED 1800
3 BIKE RED 300

i.e. four columns, one for identification, one for vehicle type, one for
color and one for prize. The 'vehicle type' and 'color' are ´pre-defined
scroll bar lists.

Now, what I want to do is to have anothe work sheet where all blue vehicles
appear (i.e. the full row as in ID, VEH., COL, PRIZE). I.e the list in the
'blue work sheet' would be
1 CAR BLUE 900
but if I change the RED car to BLUE the list would be
1 CAR BLUE 900
2 CAR BLUE 1800

Does anyone know how to achieve this?

Thanks!
 
Hi,

Create a pivot table with ID and vehicle number in the row area, colour in
the page field area and prize in the data area. In the pivot, get rid of
the subtotal rows. Now while your cursor is in any cell in the pivot table
click on the Show pages in the pivot table drop down button (in the pivot
table toolbar). Click on OK. This will create two separate tabs - one for
blue cars and the other for red cars. If you change the colour in the
source data, you just need to refresh any one of the sheets - blue or red
and all pivots would update.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Back
Top