How to get info from one sheet to another automatically in a workbook without vlookup

Good morning,
I have posted this before but maybe I didn't work it right or something. This is due wednesday and I'm very stuck. I'm attaching my workbook, what I am trying to do is create an invoice tracker for this company. On the Invoice Main tab the assistant creates an invoice which then the technician can fill out on the Invoice tab, the drop downs take them to their invoice and then they fill in what they sold. How the technician gets paid is based on the hours sold, so I included that. Now where I am stuck is, I can't figure out how to get those hours sold from the Invoice tab to pull over automatically to the Hours Sold tab. They want it to calculate per invoice and also calculate totals.
I was thinking a simple vlookup to pull over but based on what? the sheets have nothing in common except the invoice number but that is in the drop downs of the Invoice tab not on the rows themselves.
It's ok if I need to redo something to make it work or even revamp the entire workbook, I'm open to any and all suggestions. This is time sensitive so if you can help, please do! I'd greatly appreciate it, thanks!

I'm not quite sure that I am pulling the pieces from the right place. Also I do not understand how your business process works so I can't recommend a better layout although I suspect there is one.

Here is something that gets the information from the invoice and the main sheet and puts it on the hours sold sheet.

If you can be explicit about the process from beginning to end. I might be able to help with the second part. Basically - who fills out what and when and where does the information come from?

The macro to run is called AddInvoice.

Thank you so much, so yes, three people will be utilizing this tracker, the assistant who will use Invoices Main to create one for the technician, the technician only touches the Invoice tab to pick the work he did that day, hours sold will be where those invoices hours are housed for the third person, the boss, will only view the Hours sold to make payment to the technician. Inventory is just that, everything the company sells, the other tab is supposed to be where the details of the Invoice tab pulls to but I don't know it does that.. though it's not the issue right now anyway, just getting those invoices and hours to show up on another tab entirely to track by invoice and also totals for that specific technician.

OK, I'm going to take a look at the workbook with these items in mind.

One thing I applaud you for is the use of Excel tables. They do make things a lot easier to write formulas for and to code in VBA. However, I notice that you are adding extra rows in the tables. You don't really have to do this. If you right click on a cell in the bottom line of the table you get an option to Insert -> Table Row Above. This works if the bottom line is a total row. If you don't have a total row, simply adding information on the next available row adds that row to the table automatically.

One of the advantages of a table is that it knows exactly how many rows and columns it has, so you are never working with too little or too much data.

Here is some more information on tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel.

This is the template I was using mine from.. it was very generic so I was tweaking it to meet the company requests, perhaps I should start from scratch?

This is what was requested:

add in a worksheet for all their inventory they sell
Get rid of taxes as that is already added into the total sale price
Change customer tab to Technician. I am now thinking one technician per workbook so multiple lines are no longer needed.
On invoice tab, make it so the technician can use a drop down to choose what they sold and then it auto populate the price, description, hours etc.
Then from that invoice create a new tab that will accumulate the hours sold per invoice # and then a total amount for that specific technician, add in a column
for the boss to to put whether those invoice hours were paid or not for that technician, so per invoice and total as well.

I think that is about it, so basically I was tweaking this template to fit those needs. So now, knowing that, would you be able to take what I've done so far and
advise what I need to do to complete this? It's due tomorrow and I'm so lost.

One big problem I see is that once an invoice is made, it's "forgotten." In the example, the quantities are always 2, 1, 1, 1. The technician may fill out the invoice but there is no keeping of the history of the invoices.

I am looking at the Invoice Details. This seems to be a list of "place holders" that could be included on an invoice. The Invoice has a formula to look them up and then when you select an item, the formula goes away - pretty cool. However, I don't understand its purpose since the lookup for the description of the item comes from the inventory sheet.

The unit price comes from the Invoice Details, but this information could be included with the Inventory.

Ironically, there is no place on the Hours Sold Sheet for Hours Sold. This looks like a duplicate of Invoices – Main.

So here is what I would suggest:

Create a template sheet for the invoices set up mostly the way it is now. Under description, leave the entries blank but keep the current data validation. Item number can be calculated from the description (it appears to be what appears before the dash).

Do data validation on Qty to be a positive whole number. Look up unit price and hours from the inventory sheet.

When the people who fill in the Invoices Main completes the data entry, they select a cell on that row and click a button. This will make a copy of the invoice on a new sheet named for the invoice number.

Hours sold can then be looked up from the historical invoice sheet based on the invoice number and returned to the Invoices – Main Sheet.

I’d probably put in a pivot table against the Invoices – Main so the boss can select the date ranges and can look at hours by technician, totals due, etc.

The Invoice Detail sheet can go away, and with the hours (and pivot table) on Invoices – Main, the hours sold sheet can go away.

Your response came in as I was working on this. So I may be a bit off base.

Let’s see if this works for you.

I had to keep the Invoice Details sheet for the moment, since it is used in a formula in Column I on the Invoices Main Sheet. I don’t know what this sheet does for you yet. We will have to discuss what this information does and how to track it better in the future.

Everything revolves around the Table on the Invoices Main sheet. The person fills in the information as they do now and then selects the cell (actually any cell in the same row) that contains the invoice number to create and then clicks on the Add Invoice Button.

The program first checks to see if the invoice sheet already exists and if it does, you can either overwrite it (and lose all data) or abort the process and nothing happens.

If the sheet doesn’t exist or you choose to overwrite it, the invoice template sheet is copied to the end of the spreadsheet, and is renamed with invoice number. All cells should be locked except description and quantity. I did not change anything on the template except the lookups for price and hours – they both come from inventory now. You can change one of them back to Invoice Details if you feel the need. Do this in the template file.

The technician fills in the appropriate sheet and the hours sold are calculated on the Invoices – Main sheet.

I created a pivot table against this table. I’m not sure what you want to track, so I took a guess.

Thank you so much Dflak, I appreciate all your help on this