Hi,
I have a problem, that i can't solve, so i hope someone can help me :-)
Example:
Sheet: Users
Column A has "Yes and No"
Column B has "Names"
ex.
A B
Yes Peter Johnson
No Patrick Andersen
Yes John Smith
Now i would like to create a list in sheet [Employee] where one the users with Yes in column A is shown.
In this ex.
Peter Johnson
John Smith
No need to show yes or no.
Hope someone can help me. Tried googling for hours but haven't been able to solve it.
Kindly Rickie
Have you looked at using autofilter?
if you want to do it with a formula it would be something along the lines of that shown in the attachment.
Hi there,
Try if You can work with this formula on Employee sheet, in cell A1:
Formula:
Please Login or Register to view this content.
Put in A1 and drag down.
(Note that it is array formula and needs to be confirmed with Ctrl+Shift+Enter instead of Enter)
Book1.xlsx
Hope it helps
Regards
Miroslav R.
(If You like my solutions, feel free to add reputation.)
Hi guys,
Thank you for your response, but it's not quit what I want. I've attached my workbook, so you can se it. There is no secret data what so ever.
In the first sheet [Utgifter (gemensam)] there's a column (G) [Ansvarig] - marked green. In that column i want a droplist/list with only the [name] of the people in sheet [Deltagare] and where they have [Ansvarig] = Yes.
Have a nice day, and again thanks for your help :-)
Kindly Rickie
Hi there,
I misunderstood Your requirement, but:
Easy way to do this is to create a list of names with Yes - I created this list on sheet 'Data' with array formula:
Formula:Please Login or Register to view this content.
Then create data validation list with names from sheet 'Data' - called Data_Validation:
Formula:Please Login or Register to view this content.
and You can sse the result in cells marked green on sheet 'Utgifter (gemensam)'.
MS2015 - Copy.xlsm
Is this what You search for?
Works like a charm. Thank you very much :-)
You are welcome!
Glad that I could help!
Re: Data validation list, depending on other values in a row.
Can you help me again...learning by checking how you do it :-)
I want to use sheet [Data] again and the column [Varutyp] and then list the result in sheet [Utgifter]-[Varutyp]. When I add a list it also shows all the blanks. I don't want the blanks.
Attached the new file, would be great if you could make the changes directly in the file.
Thanks :-) Very greatful
Kindly Rickie
Hi again,
As I can see, Your list on 'Data' sheet under header 'Varutyp' is static(w/o formulas). Anyway You have 2 options to avoid display blanks in dropdown:
1) create named range for range D2:D13 on 'Data' sheet (cells with this data validation are green on 'Utgifter' sheet) - named range 'Varutyp2'
2) in case You will add something to 'Data' sheet under header 'Varutyp' and want to expand named range automaticaly, You have to use OFFSET function (cells with this data validation are yellow on 'Utgifter' sheet) - named range 'Varutyp':
Formula:Please Login or Register to view this content.
check:
HTML Code:(forgot to mention, that in 1st case when adding more new values to range, the named range will not expand automatically)HTML Code:
And if You play a little bit with dynamic named range, You wil find out, that if You have formulas in You named range, You have to do little modifications - You can s then in maned range from my previous post:
Formula:Please Login or Register to view this content.
MS2015.xlsm
Is it understandable? As I am not good in explanations...
Hope it helps
.
Actually you are very good at explaining :-) I could follow every step of it, so THANK YOU VERY MUCH! Nice to be able to get help here.
Have a super saturday :-)
Kindly Rickie
Great!You can always contact me if You have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks