ExcelBanter - copy data from master sheet

ExcelBanter (https://www.excelbanter.com/)

- New Users to Excel (https://www.excelbanter.com/new-users-excel/)

- - copy data from master sheet (https://www.excelbanter.com/new-users-excel/232720-copy-data-master-sheet.html)

copy data from master sheet

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

copy data from master sheet

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

copy data from master sheet

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

copy data from master sheet

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