LinkBack URL
About LinkBacks
Happy New Year to you all,
Hello,
I want to create multiple dependable drop down;seleced drop down cell wil not appear again in drop down. Attached here with sample wiht comment to understand my query properly
thanks
Happy New Year to you all,
Hello,
I want to create multiple dependable drop down;seleced drop down cell wil not appear again in drop down. Attached here with sample wiht comment to understand my query properly
thanks
Hello,
You can take a look at the following excellent explanation :
https://www.contextures.com/xldataval02.html
I'm not sure that's quite what the OP wants, Carim. The drop-down list needs to reflect items already chosen by not displaying them again,so the setting up of the dynamic list that feeds the drop-down is going to be a bit more complicated, but it's a starting point.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks Ali
Quite obviously, read the question way too quickly ...!!!
In order to hide used items in drop down list :
https://www.contextures.com/xlDataVal03.html
Not enough information yet.
What is the relationship between the 3 blocks of data on the left and the yellow header rowsand the block of names on the right?
There are NO dropdowns anywhere on your sheet, so I do not know what values can be found in column F. Once the book number is chosen, a simple formula will copy the serial numbers over.
For now, my biggest problem is where is the range of values that can occur in column F?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Good Morning All,
I think I have not put my question properly so its creating confusion. Let me reframe my query: Also attached new sheet
1 I have coupons (approx 30000) which I wish to distribute amongst Blocks
2 So first I will enter date manually, then select the Block Name by dropdown list
3 Once I select the block, I will select Price from by dropdown again (10/100/1000)
4 On Selection of Price I want dependable dropdown which will give me BookNo of selected Price.
Suppose I select 100 then BookNo column should show Dropdown list of 100BookNo list only
5 On selection of BookNo, I can fetch CoupnSrNo using lookup
6 Remaining Two columns I will manage by lookup.
7 After Table is complete, I will migrate all details to Inventory sheet using micros, and Table will be black for new entry
AND Main concern here
While making new entry used BookNo should not appear in dropdown else same BookNo can be issued to many
Hope this is clear
Your Excel version: is it Excel 2010 or later? Please amend your prifile to show correct version, but also tell me now.
No reply so far. Here is a version set up for Excel 2010+. It will not work if you still have Excel 2007. I can adapt it to work for E 2007 in a very short time. See if it is doing what you want. I have restricted the entries in book price to one replicate of the price. start by entering 10s in Distribution, G8 downwards. The rest of the table will auto-populate until there are no 10s left. Then you can only get values in the rest of the table if you enter one of the other price choices.
Different cell shading = different formula. I will discuss the formulae once I know which Excel version you are using!!
Here is a version that will work on Excel 2007+. The only difference is in distribution G8 column. It's probably better to use the first version if you have Excel 2010+.
To amend your profile, follow the pictorial guide:
I have updated, I have MS Office Home Edition 2016
In your profile, it says 2011 - try again!
Good Afternoon,
I think I have not put my question properly so its creating confusion. Let me reframe my query: Also attached new sheet
1 I have coupons (approx 30000) which I wish to distribute amongst Blocks
2 So first I will enter date manually, then select the Block Name by dropdown list
3 Once I select the block, I will select Price from by dropdown again (10/100/1000)
4 On Selection of Price I want dependable dropdown which will give me BookNo of selected Price.
Suppose I select 100 then BookNo column should show Dropdown list of 100BookNo list only
5 On selection of BookNo, I can fetch CoupnSrNo using lookup
6 Remaining Two columns I will manage by lookup.
7 After Table is complete, I will migrate all details to Inventory sheet using micros, and Table will be black for new entry
AND Main concern here
While making new entry used BookNo should not appear in dropdown else same BookNo can be issued to many
Hope this is clea
BTY i hv updated my version
Your forum profile is WRONG - its says 2011, but in post #10, you say it's 2016. Please update it correctly!
Yes I hv changed now pl chek if its correct now
Well, it's different again! What does it say when you launch the program on the splash screen? Excel 365 or Excel 2016? They are not quite the same thing.
jimmiboy. rather than telling me AGAIN what you new requirement is AND posting the SAME sheet as in Post 6, Please look at my response in Post 8 and tell me what is wrong with it.
Glenn,if
Yes it seems its workable pl let me check properly and let you know, also pl advice on when i will use 35000 rows data , only change in range will do or some other formula??
Hello,
sorry I am not used to pl advice on update what should i wrtie ther to update properly , I have MS Office 356 2016 Students version and Win 10
Check it over. Make sure it is OK. then I will explain formulae (if needed) and explain what needs to be done to cover your range.
Glenn,
yes its working so my first part is over, now may VBA and how to control duplicate ?? I will be issuing almost 1000-2000 coupns to each, besides rows in data sheet will be add if coupn demand increase....
What do you mean by "how to control duplicate". There are NO duplicates possible on this sheet (I think!!). Show me where duplicates have occurred.
Agreed, but as i told once i make all entries for one vendor i wish to migrate that data to other sheet and table shouuld be blank, for second vendor same BookNo should not appear again.Besides for yr formula i have to disribute coupns Sr No wise.
So I want to select BookNo by dropdown
To me, it makes more sense to keep all the data in one place... and then filter the data onto your inventory sheet...
Like this (it can also be filtered by date, if required):
this is a good solution, but there will be two guyes who will be disributing the coupns, they will just note down BookNo to whom it issued and date, and after closing entries will be done. so i feel, making entry first then issue the Book will be time consuming and it will be big choas ...........
I'm out of ideas! Good luck and over to others to step in...
hey,
can you pl advice on drop down Price and dependeable dropdonw on BookNo, I will select the Price and all BookNo list of that Price should appear only remainig three colomn i will manage be lookup and duplication willl manage manually
Not sure that I understand the full request, however as to post #26 the source for the Price drop down list could be the same as in F8:F17 on the Distribution sheet, i.e.: =Price
The source for the Book No. dependent drop down list could be:Formula:
Please Login or Register to view this content.
Note that the above is based on the file attached to post #23 by Glenn Kennedy.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks