L
Legacy 194999
Guest
I have a costing workbook and one of the pages is titled "AllJobs", where info for all incoming jobs is entered as is completed info for that job (such as labor hrs & other expenses). Each incoming job is assigned a job number when it is entered on the "AllJobs" sheet. Job numbers are based upon the yr rcvd & in which order they are received. For example, all jobs rcvd in 2015 will have a job number that begins with 15 and will follow in numerical order from there. Job 1501 indicates the first job rcvd in 2015, 1510 would be the tenth job rcvd in 2015, job number 1422 would be the 22nd job rcvd in 2014, etc. From that sheet, information automatically flows into other sheets, and everything is based on the job number. So, in the "CostingSheet", I enter 1509 in the designated job number cell, and all the information from "AllJobs" for that job automatically flows into the "CostingSheet", as well as other information from other sheets (such as the overhead figure for that yr, etc, etc.) From that Costing Sheet I can see the Total Revenue for that job, the break even figure, the Net Profit, Net Profit Margin, labor costs, etc, etc. All works very well except for one thing. Sometimes a customer will include two jobs on one Purchase Order....two different Part Numbers, with different due dates, requiring different materials, different processes, different labor, etc. In those cases, the job number is assigned as two jobs but indicating they're on the same Purchase Order, as was this job in 2014, for example: The 9th job of 2014, which would normally be numbered 1409 but because the PO included two different jobs, they were numbered 1409a and 1409b. That still works okay except for things that flow from the "MASTER-Calculations" sheet, which include things like Workers' Comp rates for that year, that year's individual labor costs for each operator, etc., which change from year to year. This is the formula I'm using to tell the Costing Sheet where to look for the labor rate based on the job number, which is based on the year. (B6 is the cell where the job number is entered, <1399 would be all jobs before 2014, <1499 would be all 2014 jobs, >1500 would include all 2015 jobs) =IF($B$6<1399,'MASTER-Calculations'!$B$24,IF($B$6<"1499",'MASTER-Calculations'!$C$24,IF($B$6>1500,'MASTER-Calculations'!D24)))) As the formula is written (with "" around 1499), I get the correct 2014 information for all 2014 jobs, including those jobs w/ letters following the number, and I get the correct 2013 info for all the 2013 jobs (none of which were split/numbered like this). However, I also get 2014 information for all the 2015 jobs, no matter how they're numbered. If I take the "" away, leaving just 1499 in the formula, I get the correct information for the 2015 jobs and all 2014 jobs EXCEPT those that have a letter following the number. For those jobs, the 2015 information is returned. I've tried using both in the formula, =IF($B$6<1399,'MASTER-Calculations'!$B$24,IF($B$6<"1499",'MASTER-Calculations'!$C$24,IF($B$6<1499,'MASTER-Calculations'!$C$24,IF($B$6>1500,'MASTER-Calculations'!D24)))), but that doesn't work either. Can you please tell me how I can have the correct information fill in without needing to go back and change our whole job numbering system or change the formula anytime I need to get info for a job that has mixed numeric and text value? Sorry for the novel, hope it makes sense. I will so appreciate any help! Thank you.....
