I have a list and want to filter it with reference to a cell. e.g. list is ranging from Row 5 to 2000, I want to assigne Cell A4 as reference to filter the data. When something is entered in A4 then it should check that A5 to A2000 has that data or not if yes then filter or else don't execute and let other formula work based on A4.

I have attached the sample list. Here I have entered 1003041 in A4 and want to get the below filtered list automaticaly.

open your sheet...
Hit ALT+F11 to get to the code window
dbl click the SHEET1 to open that code window

paste this in:

Code:

---

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = Range("b4").Address Then
        Selection.AutoFilter Field:=1, Criteria1:=Range("A4").Value
    End If
End Sub

---

now enter a value and hit tab (or click in B4) entering B4 will activate it

Quote:

---

Originally Posted by StaticFX

open your sheet....
Hit ALT+F11 to get to the code window
dbl click the the SHEET1 to open that code window

paste this in:

Code:

---

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = Range("b4").Address Then
        Selection.AutoFilter Field:=1, Criteria1:=Range("A4").Value
    End If
End Sub

---

now enter a value and hit tab (or click in B4) entering B4 will activate it

---

Great buddy... it works like bullet..

But I want to go one step furter.

Now I have two sheets and want to get this filtered data in other sheet. I have attached again other sheet in this sheet I want to enter data in sheet P in cell C7 and want to get the filtered data should be copied in Sheet P (as shown) from sheet Detail. Please note that Sheet Detail would be hided for user of this file.

As shown in sheet I have entered 1002707 in C7 of Sheet P and want to get the data from Sheet Detail to be pasted in Sheet P from G24 to end as rquired.

Here's a version that does not require a macro. It is using an array to watch the Detail sheet and draw the list in realtime. If you change the employee code, the list will reorient automatically.

This is accomplished with array formulas and a helper cell on the Detail sheet that tells the chart on "P" how many matches to bring over.

The arrays work great, but if you had 1000s of cells with arrays in them, each one evaluating 1000s of rows of data on the Detail sheet, your sheet might start to slow down. If that's not a problem for you, then this is ready to use, no macro needed.

If you decide you don't like array formulas, but don't mind adding an entire helper "column" on the detail sheet, I could make this even more dynamic and remove the arrays on the P sheet. It still wouldn't require a macro, but the helper column would allow us to create a unique "index" and with that we can use simple INDEX/MATCH formulas to create the chart on the P sheet.

INDEX/MATCH formulas are so fast you could have 10s of thousands of them going on with no noticeable slowing of the sheet.

Let me know if you want to see the version without the arrays.

Quote:

---

Originally Posted by JBeaucaire

Here's a version that does not require a macro. It is using an array to watch the Detail sheet and draw the list in realtime. If you change the employee code, the list will reorient automatically.

This is accomplished with array formulas and a helper cell on the Detail sheet that tells the chart on "P" how many matches to bring over.

The arrays work great, but if you had 1000s of cells with arrays in them, each one evaluating 1000s of rows of data on the Detail sheet, your sheet might start to slow down. If that's not a problem for you, then this is ready to use, no macro needed.

If you decide you don't like array formulas, but don't mind adding an entire helper "column" on the detail sheet, I could make this even more dynamic and remove the arrays on the P sheet. It still wouldn't require a macro, but the helper column would allow us to create a unique "index" and with that we can use simple INDEX/MATCH formulas to create the chart on the P sheet.

INDEX/MATCH formulas are so fast you could have 10s of thousands of them going on with no noticeable slowing of the sheet.

Let me know if you want to see the version without the arrays.

---

Hi JB.. good to here you again..

I would go for without arrays because we work on systems which are very old & slow.

As I said, the arrays are fine (even on older systems) as long as you limit how many columns are trying to use them.

Anyway, here's the same sheet again with a dynamic chart on sheet P that uses no arrays at all. All we did was add an "index" column in the Detail. Whatever employee code you select will cause unique IDs to appear for the matching rows in the index, and a simple INDEX/MATCH formula brings them over starting at 1 and up to the MAX index value.

Quote:

---

Originally Posted by JBeaucaire

As I said, the arrays are fine (even on older systems) as long as you limit how many columns are trying to use them.

Anyway, here's the same sheet again with a dynamic chart on sheet P that uses no arrays at all. All we did was add an "index" column in the Detail. Whatever employee code you select will cause unique IDs to appear for the matching rows in the index, and a simple INDEX/MATCH formula brings them over starting at 1 and up to the MAX index value.

---

Thanks a ton!!

It works perfectely fine..

Is it possible to put a password to see any data i.e. if any employee want to see his data then he need to enter his assign password.

If yes then can change password option be given and changed password would be saved in a different (hidden) sheet.

No, rather than go through all that, just create your separate "Front sheet only" version for each employee. Put their employee ID in the key cell, then pwd protect the sheet completely, no changes are possible.

Save it with their info showing and it is drawing from your separate DETAIL sheet. Give them that personalized view only sheet.

Now anytime they open it, all they can do is read it. The data updates when they open it, and that's it. No one can see anyone else's data without going and opening their own book that you gave just to them.

Much simpler.

JBeaucaire:

I know the posts is a year old, but I've been trying to do almost same thing as original poster and been studying the excel files that have been put out.

Except, now that I try to change the formulas to the way I need it, I have ran into some problems.

the formulas in p worksheet has { and }
like: {=IF(ROW()-24>Detail!$B$2,"",INDEX(Detail!B$1:B$1000,SMALL(IF (Detail!$A$1:$A$1000=P!$C$7,ROW($A$1:$A$1000),""), ROW()-24)))}

In the same worksheet, if I try to mess around with the formula, excel automatically deletes the { and }. And the formula doesn't work. It gives a #VALUE! Error. Although I didn't delete the { } myself.
Then if I try to enter { and } manually, it won't let me.

First off, what do { and } do in excel?
And how can I input it in the excel file and make it work?
Thank you so much!

Excel adds the braces { } on its own when you enter the formula as an array by pressing CTRL-SHIFT-ENTER instead of just ENTER. This causes the to function evaluate in a completely different way, once for every possible combination of values.

After you get an array working in the first cell, then you copy it down the column to get the other possible answers in the array.

HI,

I have been trying hard to read all the posts here and find a solution to my problem but am not able to do so.. So please help.

I have attached an excel sheet to explain my doubt.
In the 'summary' sheet I have created a button called Email status. When I click on this button I should be able to pick the rows from sheet B & sheet D where the status is "In Progress" [Column P] and send an email. The output should be diplayed in the body of the message.

Thanks & Regards,
Sachin