How to do things in Excel

[Curtis]

Have a design with Excel that has 3 sheet, first one is my
job report of a job that was done:
A1 = Invoice amount B1 = $3000
A2 = Total supplies B2 = $1000
A3 = Emp wages B3 = $1500
A4 = Job Profit B4 = $500

A8 = Employee Hr
A9 = Name B9 = Joe Doe
A10 = Rate B10= $10.00
A11 = Hours B11= 34
A12 = Pay B12= $340.00

On second sheet, I am trying to make a spreadsheet so
it'll keep the record of the jobs for the year. The
worksheet,(first sheet), which is what I do my invoice on.
So every job its done, its overwrite on it and print out.
What I like to do is have sheet two to be my record for
what jobs I did for the year. Needs a way to keep a
record, don't need to be for whom but for the total amount
of every thing in each cell for each item. Any help?

 

[Paul Corrado]

Curtis,

The process of automatically copy/paste the information from Sheet 1 to
Sheet 2 requires a macro, and while not difficult can be avoided with a
slight modification to your application.

Instead of using Sheet 1 for input, enter your job info on sheet 2. Set up
sheet 2 with the sheet 1 column A information as your column headings
(including Job number in Col A) and list job details down the rows.

To create your invoices leave Sheet 1 set up as is and use a VLOOKUP to
return the required information for column B.

VLOOKUP("job
#",OFFSET($A$1,1,0,COUNT(sheet2!$J:$J)-1,10),MATCH(A1,Sheet2!$A$1:$J$1),Fals
e)

HTH

PC

 

[Paul Corrado]

Curtis,

I didn't mention it, but you need to include the Job number somewhere on
your invoice such as A1, (its a good practice for tracking too) and then
change the formula to reference that cell.

Also, I made an error and COUNT should be COUNTA.

This would work better.

VLOOKUP($A$1,OFFSET($A$1,1,0,COUNTA(sheet2!$A:$A)-1,COUNTA(1:1)),MATCH(A1,Sh
eet2!$A$1:$J$1),False)

PC