my sheet1 is master sheet where data related to all continent is entered. e.g.
A B C D .......
1 America US Big 1
2 Europe Britain Big 3
3 America Canadan Medium 7

i want my sheet2 to get all information if column A contains america.
(i.e. row 1 & row 3 data) ...i.e. sheet 2 is america specific sheet..
similarly sheet3 to get all data if Column A contains Europe (i.e. row 2
data).
It should update automatically whenever i enter data in master sheet.
i dont want to use macro.

Thanks in advance

I would use a macro but try using VLOOKUP

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"aditya" wrote in message
...
my sheet1 is master sheet where data related to all continent is entered.
e.g.
A B C D .......
1 America US Big 1
2 Europe Britain Big 3
3 America Canadan Medium 7

i want my sheet2 to get all information if column A contains america.
(i.e. row 1 & row 3 data) ...i.e. sheet 2 is america specific sheet..
similarly sheet3 to get all data if Column A contains Europe (i.e. row 2
data).
It should update automatically whenever i enter data in master sheet.
i dont want to use macro.

Thanks in advance

hi Don,
please let me know how can i use vlookup to help me out.

"Don Guillett" wrote:

I would use a macro but try using VLOOKUP

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"aditya" wrote in message
...
my sheet1 is master sheet where data related to all continent is entered.
e.g.
A B C D .......
1 America US Big 1
2 Europe Britain Big 3
3 America Canadan Medium 7

i want my sheet2 to get all information if column A contains america.
(i.e. row 1 & row 3 data) ...i.e. sheet 2 is america specific sheet..
similarly sheet3 to get all data if Column A contains Europe (i.e. row 2
data).
It should update automatically whenever i enter data in master sheet.
i dont want to use macro.

Thanks in advance

If desired, send your file to my address below along with this msg and clear
explanations with before/after examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"aditya" wrote in message
...
hi Don,
please let me know how can i use vlookup to help me out.

"Don Guillett" wrote:

I would use a macro but try using VLOOKUP

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"aditya" wrote in message
...
my sheet1 is master sheet where data related to all continent is
entered.
e.g.
A B C D .......
1 America US Big 1
2 Europe Britain Big 3
3 America Canadan Medium 7

i want my sheet2 to get all information if column A contains america.
(i.e. row 1 & row 3 data) ...i.e. sheet 2 is america specific sheet..
similarly sheet3 to get all data if Column A contains Europe (i.e. row
2
data).
It should update automatically whenever i enter data in master sheet.
i dont want to use macro.

Thanks in advance

Here's a non-array formulas set-up which should appeal to you

Assume your master sheet is named: x
with source data as posted running in A2:D2 down

List all the uniques for col A in F1 across, eg: America, Europe, etc
Put in F2: =IF(F$1="","",IF($A2=F$1,ROW(),""))
Copy F2 across/fill down to cover the max expected extent of source data,
say down to row 200?

In another sheet, say Sheet2,
Let's earmark A1 for the input of the desired unique value, eg: America

Put in C1:
=IF(ROWS($1:1)COUNT(OFFSET(x!$E:$E,,MATCH($A$1,x! $F$1:$IV$1,0))),"",INDEX(x!A:A,SMALL(OFFSET(x!$E:$ E,,MATCH($A$1,x!$F$1:$IV$1,0)),ROWS($1:1))))

Copy C1 across by 4 cols to F1, fill down to the same extent as done in x.
Cols C to F will return the required dynamic results, ie only the source
lines dependent on the input in A1, all neatly packed at the top. You have
fingertip control via the input in A1.

And if you really want each sheet to house all the lines for a particular
unique value, just make as many copies of Sheet2 as required (dress/format
Sheet2 up nicely to taste first), then input each unique value into A1 (a
one-time job)

Success? Celebrate it, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"aditya" wrote:
my sheet1 is master sheet where data related to all continent is entered. e.g.
A B C D .......
1 America US Big 1
2 Europe Britain Big 3
3 America Canadan Medium 7

i want my sheet2 to get all information if column A contains america.
(i.e. row 1 & row 3 data) ...i.e. sheet 2 is america specific sheet..
similarly sheet3 to get all data if Column A contains Europe (i.e. row 2
data).
It should update automatically whenever i enter data in master sheet.
i dont want to use macro.

Thanks in advance

Thank you Max. I need further help from you.

I have another sheet (named today data file) which should contain all data
which has been entered in master data sheet today only.I am using today ()
function.Now in master sheet if america is found, I want to copy entire data
to today data file say to A15 across row.
If Europe is found in master sheet,i want to copy entire row to today data
file say to A200 across row.

Today's data sheet sholud not contain yesterday's daya. Pls help.
Thanks in advance

"Max" wrote:

Here's a non-array formulas set-up which should appeal to you

Assume your master sheet is named: x
with source data as posted running in A2:D2 down

List all the uniques for col A in F1 across, eg: America, Europe, etc
Put in F2: =IF(F$1="","",IF($A2=F$1,ROW(),""))
Copy F2 across/fill down to cover the max expected extent of source data,
say down to row 200?

In another sheet, say Sheet2,
Let's earmark A1 for the input of the desired unique value, eg: America

Put in C1:
=IF(ROWS($1:1)COUNT(OFFSET(x!$E:$E,,MATCH($A$1,x! $F$1:$IV$1,0))),"",INDEX(x!A:A,SMALL(OFFSET(x!$E:$ E,,MATCH($A$1,x!$F$1:$IV$1,0)),ROWS($1:1))))

Copy C1 across by 4 cols to F1, fill down to the same extent as done in x.
Cols C to F will return the required dynamic results, ie only the source
lines dependent on the input in A1, all neatly packed at the top. You have
fingertip control via the input in A1.

And if you really want each sheet to house all the lines for a particular
unique value, just make as many copies of Sheet2 as required (dress/format
Sheet2 up nicely to taste first), then input each unique value into A1 (a
one-time job)

Success? Celebrate it, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"aditya" wrote:
my sheet1 is master sheet where data related to all continent is entered. e.g.
A B C D .......
1 America US Big 1
2 Europe Britain Big 3
3 America Canadan Medium 7

i want my sheet2 to get all information if column A contains america.
(i.e. row 1 & row 3 data) ...i.e. sheet 2 is america specific sheet..
similarly sheet3 to get all data if Column A contains Europe (i.e. row 2
data).
It should update automatically whenever i enter data in master sheet.
i dont want to use macro.

Thanks in advance

Start a new thread for your new query ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---