How can I create a dynamic view (filter) of a worksheet?

[c7s9]

I'm trying to modify a an excel file to improve its functionality, but unfortunately I don't have much experience with excel.
To explain my problem, let's say I have 2 worksheets: WS1 and WS2. WS1 contains all the data entered by the user. The first column contains integer numbers. I would like WS2 to contain a dynamic view of WS1. In particular WS2 should contain all the lines in WS1 whose first column contains "1". The content of WS2 should change automatically every time WS1 is updated.
Can I achieve this functionality with standard excel functionalities?
Thanks!

 

[Akashwani]

Hi and welcome to MrExcel.

If I understand you correctly, does this work for you?....

Sample data......

Excel Workbook
	A	B	C	D
1	Number	Data1	Data2
2	12345	Bill	Red
3	2345	Ben	Blue
4	3456	Bob	Green
5	134456	Ted	Pink
6	54321	Tim	Yellow
7	55555	Tod	Green
8	551555	Tom	Orange
9
WS1

Example results.......

Excel Workbook
	A	B	C	D
1	Number	Data1	Data2
2	12345	Bill	Red
3	134456	Ted	Pink
4	54321	Tim	Yellow
5	551555	Tom	Orange
6
WS2

The formula in A2 needs entering with ctrl shift enter NOT just enter, it can then be copied across and down.
You will need to change your cell references to suit your layout.

I hope that helps.

Good luck.

Ak

 

[c7s9]

Thank you, this is exactly what I needed!
I was not aware of array functions...

 

[Akashwani]

Hi,

I'm pleased it worked for you and thanks for the feedback.

Take a look here for some excellent Excel videos on array formulas, Mike also has 1000's of very helpful Excel videos.

excelisfun -- Excel How To Videos - YouTube

Ak

 

[vladutnea]

This thing is amazing, it worked very good for me, until I had the following problem: I needed an exact match. For example, if I have the number "8" for filtering, I do not want to have the results containing "8", like "28", "58", or "83". It is this possible?

 

[Akashwani]

Hi vladutnea

Does this help?....

Sample data....

Excel Workbook
	A	B	C	D
1	Number	Data1	Data2
2	8	Bill	Red
3	2345	Ben	Blue
4	8	Bob	Green
5	134456	Ted	Pink
6	54321	Tim	Yellow
7	55555	Tod	Green
8	8	Tom	Orange
9
WS1

Example results....

Excel Workbook
	A	B	C	D	E
1	Data1	Data2		Number
2	Bill	Red		8
3	Bob	Green
4	Tom	Orange
5
WS2

The formula in A2 on sheet WS2 needs to be entered using ctrl-shift-enter and NOT just enter.
You will need to change the cell references and sheet names to suit your layout.

I hope that helps.

Ak

 

[vladutnea]

Thank you very much for your help people. Special thanks to Akashwani . I have been using this formula for some time, it is really useful. Sorry for late reply.