Can someone please tell me how I tell excel 2003 to display the value selected from an autofilter list into another cell, be it on the same sheet or another sheet? I have attached a dummy workbook. On sheet1, I would like cell A3, B3, C3 & D3 to display the value selected from their respective list. Any help would be greatly appreciated. Thanks Mikal. (It's only easy if you know how!)
Have a look at this article:
http://www.rondebruin.nl/copy5_2.htm
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
thnQ for your qik reply NBVC. I have seen this from doing a search in the forum. Though I must admit I am very new to excel, and I didn't understand the code written and what it does or how to embed it into my sheet. Can you explain it to me please? Thanx Mikal
do you mean like this.? formula in a3 is array entered with ctrl+shift+enterthen dragged across to d3
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
hi Martin, thanks this is kind of what I want to do. Is there any way that only the column(s) filtered on, will the data be displayed above. If the column hasn't been filtered on, then there is no data displayed above the respective column. Does this make sense.
I have included another workbook: mikal_autofilter_Data.xls
For example: if I select 'M' from GEN (Col C) and, 'URD' from LANG (Col H), then C3 should display 'M', and H3 should display 'URD', with the other cells above the columns blank. Is this possible?
Thank you again. Mike
I am not sure that the code on Ron's page would do what you want anyways....
.. without some sort of event macro (to be created by someone else, if it is possible), then the only way with formulas is to perhaps use a row above that you enter a marker in, like an "x" to indicate which items you are filtering...
then formula provided by Martin would change to:
=IF(A2="X",LOOKUP(REPT("Z",255),IF(SUBTOTAL(3,OFFSET(A5:A15,ROW(A5:A15)-ROW(A5),0,1)),A5:A15)),"")
confirmed with CSE key combination and copied across.
Enter an X in row 2 where you want answers shown in row 3.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thanks again Martin for your work on this. I have tried your second idea of placing a 'X' in row 2. Which will be above all of the columns that can be filtered on. This has produced the same results as your first suggestion, with data being displayed in the column headers of row 3, whether the column has been filtered on or not!
I didn't realise how tricky this was going to be!
When you say a macro, what kind of guidance could you give in relation to this, in trying to solve this?
Thanks again, Mike.
i never mentioned macro! i blame NBVC lol . i couldnt code that its out of my depth.but
NBVC was suggesting you only put the X as and when you filter a column not all of them!
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
It's a bit rough, but try the attached.
Romperstomper, wow :-) thank you ever so much. It works! Can you tell me how you did it, (you'll need to explain step by slow step so I can fully understand what is happening)
You said 'this is a bit rough' does that mean it can be done in a better/simpler/easier way?
Have a great weekend, you've just made someone really Happy :-) Mike.
You'll need to look at the code in Module1 of the workbook to see how it works. It's rough in the sense that I didn't test it particularly - just cobbled together some bits of code I had for something else - so there might be circumstances where it doesn't work. For simple filters it should be fine though.
Thanks again for your help RS. Where on the net is a good place to start to learn the basic of VBA/Macros in excel? I would really like to understand what is happening & why. Can I just copy your coding into my 'real' data file? Cheers again Mike
I don't honestly know the best beginner websites - I'm a Luddite, so I like books myself.
You should be able to copy the coding as is - the function just needs to be passed a cell in the correct filter column of the filter range to work.
Okay thanks RS, which easy to read book(s) would you suggest that explains well?
Can I ask 2 more questions about my sheet please: 1. How do I create a 'reset' button on sheet1 to display all of the data in its original state (ie- no data filtered on). & 2. How do I display the information in row3 on sheet1 in row2 in sheet2 or column2 in sheet2? Thank you again, I can't believe I'm working on this first thing Saturday morning, I'm so excited! :-) Enjoy your weekend. Thanks again Mike.
Originally Posted by romperstomper
If you don't know any VBA, I'd start with a Dummies book, then move on to something like John Walkenbach's Power Programming series, or the Wrox Press Excel 2007 VBA book (don't get the 2003 version)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks