I'm trying to create a square footage spreadsheet that gives me an idea of
how much material I can process in a week's time.
I have a list of jobs on the left side of my spreadsheet and on the right
side starting at column 'J' I have hours listed horizontally for example: an
8 hour day from 6-2pm would be labeled 6, 7, 8, 9, 10, 11, 12, 1, 2.
I list the sq ft in the 'F' column and then my cells count out by the
amount of sq ft until it reaches the total then stops. So, if I do 100 sq ft
per hour and I put in cell F2 that my sq ft for a job is 500, then cell J2 on
that row will start counting at one until it reaches 5 in cell N2 column then
stops.
Then when I list my next job, I put the value of 1200 into F3, my count
starts in cell O2 and counts from 1 to 12 representing the hours to process
that job. My problem occurs here when I have to have a break between the days
of the week.
Cell A1 would be labeled Monday and depending on the sq ft of jobs in
Monday, Tuesday may start at cell A5 or A10. In the above example where my sq
ft would only allow me to fit two jobs cell A5 would be labeled Tuesday and
my next job would be entered on row 6. So, in the example above I run out of
hours in Monday and still have blocks to fill in for Job#2 because Job# 2
ended with number 4 at cell R2, so my spreadsheet needs to duplicate the
information from cells A3 to F3 and put it in A6 to F6, while cell J6
continues counting at 5 and stops at Q6 with the number 12.
I currently have a ton of cells with if/then formulas which is enough to get
by , but I have to up date so many things manually, I know there has to be a
way to let the computer do more of the work, but I'm not very familiar with
programming in the Excel environment. Sorry, I know this complicated, I hope
the above information makes sense. Thanks to anyone who can help.
|
Similar Threads
