I'm a complete idiot when it comes to Excel so I'm not even sure where I would start to create a book that could do this but I am trying to make it so when the "Status" of a position is set to "Open" it will list the entire position row on the first sheet labeled "Open positions" It would also be nice if someone could tell me how to make it so my "Days Open" Column won't show a negative number if there isn't an end date yet. My workbook is attached any help or advice is appreciated!
How about this in I3:
=IFERROR(DATEDIF([@[Position Open]],[@[Offer Accepted]],"d"), 0)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Yes! That looks a lot better than a big negative number. Is there a way for it to display how many days it has been compared to the current date and then stop counting at whatever date is put in to the "Offer Accepted" column. If not no biggie, again i'm kind of 'slow' when it comes to excel so I appreciate all of the help already, thanks!
Unless you're willing to add VBA to your workbook, I think you're doing this backwards. You should be inputting your data on a SINGLE sheet with the name of the facility in column A so everything is in one place.
Then you won't even need the other sheets, not really. You can turn back on the filters at the top of the table and look at just one facility at any time, or just OPEN jobs at all locations, you can filter any way you want with no extra plumbing at all.
This formula will count the days open so far and stop counting when a job is accepted/closed.
=IFERROR(DATEDIF([@[Position Open]],MIN(TODAY(),[@[Offer Accepted]]),"d"), 0)
That formula is awesome, thank you...
That's not a bad idea... I wish I would've thought of that before I went to the trouble of making all the sheets.
I do think I want to keep the separate sheets if possible because they do contain useful information and there will be even more at the top of the page.
I know, I appreciate that, but in my opinion that information at the top is also better served as "fields" in your database.
TYPICAL would be:
1) A SITE sheet listing all your locations and key information for each site. This table would be comprised of what you have at the top of all your individual sheets.
2) A Database sheet where you list the job openings. The only new field needed from what you currently have would be the "SITE" in column A.
3) A Report sheet. This is what you're working on now, but in this setup it is MUCH simpler to do. All the data is coming from the Database, so pulling in information based on ANY criteria becoming exceedingly simpler.
a-show all data for one site
b-show all data for one STATUS
I guess I don't want to try too hard to dissuade you from your current path too hard, but keeping what you've done does mean a LOT more plumbing to do anything meaningful. Look at this first project need, it's going to force you into using macros way earlier than would be necessary in a typical setup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks