Hello,
Glad to join this community!
I need your help with a resource planning tool. I want to make a macro that would remove an asset from a pool of available assets after I choose it from a drop down menu for Job A, so that Job B hasn't got an option of chosing it once it is already assigned.
your help and ideas are very appreciated
I have achieved this with Dynamic Named Ranges-
Check the attached files:-
Regards,
Vikas Gautam
Excel-buzz.blogspot.com
Excel is not a matter of Experience, its a matter of Application.
Say Thanks, Click * Add Reputation
Thank you very much for your reply. Any chance you could elaborate on the formula you used?
INDEX($B:$B,SMALL(RowArr,ROW($A3)))
Truly grateful.
Pool
=Sheet1!$B$3:INDEX(Sheet1!$B:$B,MATCH(REPT("z",100),Sheet1!$B:$B,1))
Selected
=Sheet1!$D$3:INDEX(Sheet1!$E:$E,MATCH(REPT("z",100),Sheet1!$D:$D,1))
RowArr
=IF(COUNTIF(Selected,Pool),FALSE,ROW(Pool))
ItemsLeft
=Sheet1!$J$2:INDEX(Sheet1!$J:$J,MATCH("",Sheet1!$J:$J,0)-1)
Here is the explanation:-
The Named Ranges- Pool & Selected are dynamic ranges where Pool covers Column B all Pool Items and Selected covers columns D & E.
Now the Named Range RowArr. Inside the IF construction I have used.
COUNTIF(Selected,Pool)
to produce an array of count of each Pool item in Selected range. The resultant would look like this.
If Function will then parse each 0 to false and each Non zero element (1) to corresponding Row Number in the array produced by Row(Pool). It will produce the following array:-
Please Login or Register to view this content.
Now I have used following formula in J2 and Drag down:-Please Login or Register to view this content.
=IFERROR(INDEX($B:$B,SMALL(RowArr,ROW($A1))),"")
Row($A1) has inside a relative row reference ($A1) which returns incremental row numbers when dragged down. So that's why Small function returns 1st, 2nd, 3rd small number in the RowArr and so on (when dragged down). That’s how the ItemLeft list has been made.
Now Named Range - ItemsLeft will dynamically selects the Items Left. And is used in the data validation for listing purposes. I have improved this Named Range to ignore blanks. So you would like to reconsider this portion.
Check the attached file:-
Dear all,
as a final product I would like to be able to configure different jobs with combination of classes selectable in from a dropdown list. For each job on the same row as the selected class, I would like to be able to chose an item from a corresponding class (please forgive me if I am not being clear, but it is quite apparent form the attachment).
Kindly take a look and see if you can help
If I understood you requirement correctly, then the attached file will satisfy you.
Vikas,
thanks a lot for your help.It works great for the Job A, but if I insert a copy, job B, it is not working.
I have corrected the issue.
Check the attached file and tell if it was what your were trying to achieve:-
Vikas,
I apologize for not expressing clearly enough.The idea is that I can insert any number of jobs and the formula should still work. If I insert job C, D etc. , the I will be facing the same problem.
many thanks for your help
I have shifted the Helper columns to new sheet.
Check the attached file:-
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks