Copying a set of info from a different sheets

  • Thread starter Thread starter Laila
  • Start date Start date
L

Laila

Hi, please help

I really need your help.

I am trying to copy a set of info from eg. sheet2 to sheet1.

The problem is, I have to create a drop down list in sheet1 to brin
the infor from other sheets to sheet .

However, I manage to create the drop down list usin
Data-Validatation-List. But how do I set my switch do copy data t
sheet1.

For example - Sales value for 4 months say Jan - April ( Their are i
different Sheets). You do not want to select the sheets to see th
data. You want to create a dropdown list on a new sheet( eg Sheet5
which will fetch the sheet you want and display the entire data i
Sheet5.

Help is really appreciated
 
If I've read your intent correctly,
maybe something along these lines may work ..

Example: Assume we have

In sheet: Jan
------------------
(cols A to C, data from row2 down)

Name UnitsSold Sales
John 100 500
Peter 200 600
Steve 150 300

In sheet: Feb
------------------
(cols A to C, data from row2 down)

Name UnitsSold Sales
Peter 50 700
Steve 80 900
John 90 200

Note:
-------
Tables in sheets: Jan, Feb are assumed identical in structure
Names in col A are assumed unique but can be in any order
Col A in both sheets must be Names, although the order
in cols B & C in either sheet could be either Sales or UnitsSold

In sheet: Summary
-------------------------
Assume the corner cell A1 contains a DV
to select either: UnitsSold or Sales

DV Settings:
Under Allow: select List
Under Source, put: UnitsSold,Sales

Assume ..
B1:C1 contains : Jan, Feb
A2: A4 contains the names: John, Peter, Steve (in any order)

The layout will look like:

<DV> Jan Feb
John
Peter
Steve

Put in B2:

=IF($A$1="","",IF(ISNA(MATCH(TRIM($A2),INDIRECT("'"&B$1&"'!A:A"),0)),"",OFFS
ET(INDIRECT("'"&B$1&"'!$A$1"),MATCH(TRIM($A2),INDIRECT("'"&B$1&"'!A:A"),0)-1
,MATCH($A$1,INDIRECT("'"&B$1&"'!$1:$1"),0)-1)))

Copy B2 across to C2, fill down to C4 to populate the table

If the DV selection made in A1 is: Sales,
you'll get the extract for Sales from the sheets: Jan and Feb

Sales Jan Feb
John 500 200
Peter 600 700
Steve 300 900

And if the selection made in A1 is: UnitsSold,
you'll get the corresponding extract for UnitsSold:

UnitsSold Jan Feb
John 100 90
Peter 200 50
Steve 150 80

If A1 is cleared, nothing will show in the table
Any unmatched names in col A will also return blanks ""
 
Back
Top