Big Country Posted June 22, 2011 Share Posted June 22, 2011 Here's the background - one of my job duties is maintaining and updating our workforce forecast. basically a breakdown of planned staffing levels for the next 5 years. These can change quite a bit over the course of time as we increase/decrease contracted labor based on projects/funding. Right now, my work sheet contains all of my identifying information (position type, org strucutre, management, etc.) and also columns for each period of time that is being reported. In this case, it is a quarterly breakdown of the next two years, then annual numbers for the 3 years after that. Easy enough and it works fine, but I am exploring options to simplify. Basically if the resource is to be staffed in that time period, the column has a 1 in it, if not, it has a 0. This allows me to do all of my trending and staffing breakdowns by time period. What I would like to do is have two columns - Start Period and End Period that would be drop down lists of the available time periods we are looking at. When these fields are selected, my columns with the 1's and 0's would autopopulate accordingly, ie if the resource is planned to be staffed from Q2 2012 thru Q2 2013, then the columns for Q2 2012, Q3 2012, Q4 2012, Q1 2013 and Q2 2013 would update to a one and the rest would remain a zero. Again, not a major deal as the current system does work, but something that I think would simplify things. Alternatively, if I can't get it to work this way, any thoughts on getting the reverse to work - ie have the Start Period field look for the first column with a 1 in it and fill in the Start Period accordingly, and then look for the last column with a 1 in it and fill in the end date accordingly. Okay gurus and whomper, have at it... I probably won't be able to test until Friday, but want to get whatever ideas are out there on this subject on the table for consideration Quote Link to comment Share on other sites More sharing options...
lennykravitz2004 Posted June 22, 2011 Share Posted June 22, 2011 For starters, use Data Validation for your drop down lists. That much, I'm certain. Then you're probably looking at using a combo of vlookup or hlookup (depending on your layout), along with an IF statement. Or maybe just an IF statement. Might have to play with some formulas on this part of it. Quote Link to comment Share on other sites More sharing options...
The Irish Doggy Posted June 22, 2011 Share Posted June 22, 2011 I'm not 100% sure I understand the project. You have one master data sheet with all the info and want to create a new tab/area/worksheet where you can look only at specific dates. The new tab/area will automatically pull info from the original data sheet? Quote Link to comment Share on other sites More sharing options...
Big Country Posted June 22, 2011 Author Share Posted June 22, 2011 I'm not 100% sure I understand the project. You have one master data sheet with all the info and want to create a new tab/area/worksheet where you can look only at specific dates. The new tab/area will automatically pull info from the original data sheet? Not quite. Current - Have master data sheet with separate columns for each time period. Currently when a plan calls for a resource to be working in a particular period, it gets a 1, if they are not planned, it gets a zero. So if a resource is planned for 2012-2013 but not beyond, there are 1's in the 2012-2013 columns, but zeros for 2014-2016. Future - would like to add two columns that would display the start and end periods. Start period is where the 1 would first show up, end period is the last column that gets the 1. Using above example, would put a start date of 2012 and an end date of 2013 and then have the columns for the dates update accordingly, putting a 1 in every column between 2012 and 2013, but keeping zeroes in 2014-2016. If i can't figure that out, I'd like to see about doing the alternative, which is to fill in as I do now (zeroes and 1s in the appropriate column) but have the start period and end period columns update based on the entry (ie the first 1 shows up in 2012, so start period displays 2012, the last 1 shows up in 2013, so end period shows 2013) Quote Link to comment Share on other sites More sharing options...
lennykravitz2004 Posted June 22, 2011 Share Posted June 22, 2011 I think I have your answer. In general: Use Data Validation, employing the "List" option. This will require you to have a separate row/column with dates, which is fairly straight-forward. Use the IF and AND functions in Excel: =IF((AND($C$3<=E2,E2<=$D$3)),1,0). Of course, replace your cell locations as warranted. Rinse and repeat for all needed rows. When setting up Data Validation, select both entire columns so the drop down boxes. There are additional considerations too, such as formatting cells for date(s), using named ranges, and something else I might be forgetting... If you want, PM me with your email address and I'll send over the sample I did. Quote Link to comment Share on other sites More sharing options...
Big Country Posted June 22, 2011 Author Share Posted June 22, 2011 Sent you an e-mail. Doing the data validation on the start/end columns not an issue. pretty simple to restrict it to the column headers for my date range, which is my initial plan. It was using those selections to fill in the columns that was throwing me. Looking forward to seeing your sample. Quote Link to comment Share on other sites More sharing options...
The Irish Doggy Posted June 23, 2011 Share Posted June 23, 2011 I think you are on your way. Quote Link to comment Share on other sites More sharing options...
Big Country Posted June 23, 2011 Author Share Posted June 23, 2011 lenny's first copy appears to work like a charm - he sent me an updated version last night that utilizes quarters instead of monthly dates that I have not yet tested. At an all day meeting today, so won't have a chance to try and break it until tomorrow, but I think he has just saved me many hours of trial and error. Big time thanks. Quote Link to comment Share on other sites More sharing options...
Inhiding Posted June 29, 2011 Share Posted June 29, 2011 (edited) Instead of starting a new thread, i want to hijack this one! lol. I have a spreadsheet in excel and i found that it is not performing the operations i need it to, simple one at that. For example: in cell B66 is an input cell and I have entered in 252. Cell B57 is another manual input, in this case it is $4.50. Cel B62 is the following: =B66*B57. Now by changing either B66 to another quantity or by changing the B57 cel to another price it should update the B62 cel right? it is not doing that, unless i click on the B62 cel and hit enter again then it updates. It was working yesterday and now today it is not. any ideas?? Edit to add: Google is my friend! lol.. somehow manual calculation was switched. got it set back to automatic and it works wonders! Edited June 29, 2011 by Inhiding Quote Link to comment Share on other sites More sharing options...
CaP'N GRuNGe Posted June 29, 2011 Share Posted June 29, 2011 Instead of starting a new thread, i want to hijack this one! lol. I have a spreadsheet in excel and i found that it is not performing the operations i need it to, simple one at that. For example: in cell B66 is an input cell and I have entered in 252. Cell B57 is another manual input, in this case it is $4.50. Cel B62 is the following: =B66*B57. Now by changing either B66 to another quantity or by changing the B57 cel to another price it should update the B62 cel right? it is not doing that, unless i click on the B62 cel and hit enter again then it updates. It was working yesterday and now today it is not. any ideas?? You probably have manual calculation turned on. Go into your excel settings and change it to automatic calculation. Quote Link to comment Share on other sites More sharing options...
T_bone65 Posted June 29, 2011 Share Posted June 29, 2011 You probably have manual calculation turned on. Go into your excel settings and change it to automatic calculation. :whomper: You beat me to it :whomper: Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.