2 sheets, one with data other creates multiple "invoices"

[hmaletz07]

I am trying to update an old workbook that contains 2 worksheets. sheet 1 contains client information and hours of billable services. sheet 2 is set up to look like an invoice that goes to sheet 1 to get the data to fill in the "invoice" It is a cumbersome task to go back and forth between the sheets to set up the formulas on multiple pages within sheet 2. I know a mail merge would be super easy, but the person that uses this cannot seem to understand how to link the information between excel and word.

I have 50 "pages" on sheet 2 that mimic an invoice. how do I easily get the data into each "page" without having to directly link it through sheet 1 or manually enter the formula? Essentially, I am copying data from one row in sheet 1 and entering it in various cells in sheet 2 and then repeating this with the next row of data from sheet 1 onto sheet 2.

I know nothing about macros or vba, but can learn quickly if given direction!

Any suggestions would be fabulous!


Drowning in excel!

[queuesef]

1st step...upload sample data AND how you would like the results to look! ( you know what you are looking at, unfortunately we do NOT. There is no replacement for ACTUAL sample data!) Again, I'm not sure what you are looking at, but perhaps putting it in a Pivot table? Please read forum rules and upload file.

[hmaletz07]

I would love to, but can't upload as a newbie!

[JeteMc]

Yes you can.
To attach a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

[hmaletz07]

I hope I have attached a sample of the workbook that I have and what I want to do with it.

Thanks in advance for any help anyone can give. It will take me forever to do it manually!

[AliGW]

No, you haven't.

Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[AliGW]

Waiting for the workbook ...

[Pete_UK]

Unfortunately, there are not really written instructions at the top of the screen telling you exactly what to do, so here's the full procedure:

Firstly, prepare a sample Excel workbook, which should reflect the variations in your data and show how it is laid out, and should show a few examples of what you want to achieve.

Then you can click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

[hmaletz07]

Ok, hope this time I got it right!

Thanks!

[Pete_UK]

The attachment has worked this time.

Instead of having a variable number of "pages" in your Invoice sheet, which I imagine will change each month, would it not be better for you to select the Invoice number from a drop-down, and just have one invoice that you can print out (or save as a pdf, for example), and then select the next invoice? You would need to record the invoice number in your main sheet, perhaps in I67 downwards.

Hope this helps.

Pete

[hmaletz07]

If it is just about entering an invoice # on the second page, I think my staff person could do that. How do I go about creating the invoice # and connecting that to the second page?

[JeteMc]

I believe that this proposal is based on a similar concept to that which Pete_UK suggests.
It appears that someone has put together a concatenated list of client names in column S of the Main sheet.
Using that list put data validation in cell B1 on the Invoice sheet.
1. Produce a named range (Lst_Clients) with a Refers To of: =OFFSET(Main!$S$10,1,0,SUMPRODUCT(--(Main!$A$11:$A$61<>"")),1)
2. The Refers To of the data validation list is: =Lst_Clients
Many of the other cells in the invoice could then be populated using formulas similar to: =INDEX(Main!C$11:C$30,MATCH(B1,Main!$S$11:$S$61,0))
A few cells could be populated based on cells that are already populated, as opposed to getting values from the Main sheet.
For example populate the Total Hours Billed using: =SUM(B10:B14)
After that cells A1:G18 are selected and copied (Ctrl + c)
Select cell A19 and paste (Ctrl + v) etc.
Let us know if you have any questions.