Forum Discussion
Help With Formula
Hi Everyone!
I'm looking for help/suggestions for the following issue:
I'm trying to develop a spreadsheet to track equipment utilization and to either flag or prevent the selection of a piece of equipment that is over utilized based on an overlap in the time period. In the sample that I've provided I have a simple list of projects with start and end dates then I have a column for equipment ID and a column for the description of that corresponding piece of equipment.
The first three columns are conditionally formatted to highlight overlapping dates. The forth column is a simple drop-down list, and the fifth is has a VLOOKUP routine that corresponds with the forth column.
What I'd like to do, but can't figure out how to, is I would like to select a piece of equipment from column four for each Project but if the dates where I'd like to utilize that piece of equipment overlap I would like the spreadsheet to prevent me from selecting that particular piece of equipment.
So if I choose Equipment C-100 for Project 1 I shouldn't be allowed to choose C-100 for Project 4 because there's overlap in the dates.
Any help would be greatly appreciated.
Pls see attached file.
I don't know if this is what you wanted.
see if you can work something out of it.
thanks..
6 Replies
Pls see attached file.
I don't know if this is what you wanted.
see if you can work something out of it.
thanks..
I spoke a little too soon. It looks like if you select a piece of equipment for two overlapping projects then it leaves the equipment column blank, which is what I want it to do, but if you select the same piece of equipment for two projects where the dates do not overlap, it still does this. I would like for it to allow me to select the same piece of equipment for multiple projects as long as the dates do not overlap.
I'll try to decipher the formula to see if I can make the adjustments.
Thanks again for the help!
hope you can work it out...
if not, try reposting it.. someone here in the forum may provide a solution.
thanks..
This is great, when the user has dates that overlap and tries to select the same piece of equipment for the overlapping projects it leaves the equipment cell blank forcing the user to either select a different piece of equipment or to change the usage dates. Thank you so much.
