Excel Gurus help required

  recap 15:49 14 Jan 2003
  recap 15:49 14 Jan 2003

I have a workbook with 3 sheets on it. Information stored on sheet 3 is linked to sheet 2 (personell details). In sheet 2 the price per week is entered, I then want this information to be transfered to sheet 1 for printing as an invoice.

Could somebody please help me with the formula for this query?

thanks in advance


  cherria 15:52 14 Jan 2003

Can you be a bit more specific.

Do you have multiple entries on sheet 2 and you want to produce 1 invoice per entry or is it just 1 entry on sheet 2 and you need the value linked onto sheet 1 printing purposes?

  recap 16:16 14 Jan 2003

Yes sorry cherria it was very general.

I have 50 lines on sheet 2. Each line contains up to 10 cells with infromation that is required to produce an invoice on sheet 1 for printing.

example: sheet 2 cell A1 to be linked to sheet 1 cel reference D15, Sheet 2 cell A2 to sheet 1 d16 and so forth.
I want to be able to carry out this function for each of the 50 lines printing each one individually.

  MalcSP 16:19 14 Jan 2003

Highlight the cell which you want to be printed in sheet 1. Enter ='Sheet 2'!A1. Where A1 is the cell you have the required data in sheet 2. You can play with the cell contents as you wish.

  VoG™ 16:24 14 Jan 2003

Do you mean that you want it to loop through each of the 50 rows on Sheet2, transpose the data to the relevant cells in Sheet1, print Sheet1, get data for next row etc.

If so please confirm the start and end row numbers.

  MalcSP 16:28 14 Jan 2003

Having read your next entry.
It looks like mail merge on Word might be a better bet using the spreadsheet as a database.

  recap 16:29 14 Jan 2003

Thanks VoG™ glad one of use speaks the language, lol.

start = A6:K6,

end = A100:K100

  VoG™ 16:37 14 Jan 2003

ALT-F11 to open the Visual Basic Editor. Insert/Module.

Paste in the following code

Sub PrintInvoice()

Dim iRow As Integer, iCol As Integer

Dim InSheet As Worksheet, OutSheet As Worksheet

Set InSheet = Sheets("Sheet2")

Set OutSheet = Sheets("Sheet1")

For iRow = 6 To 100

For iCol = 1 To 10

OutSheet.Cells(iCol + 3, iRow + 14).Value = InSheet.Cells(iRow, iCol).Value

Next iCol


Next iRow

End Sub

Close the VBE. Tools/Macros/Macro, select PrintInvoice and Run.

HOWEVER, to save wasting an awful lot of paper if I've got it wrong, change one line above to this:

For iRow = 6 To 10

Once you're happy that its working, change the value back to 100.

  VoG™ 16:39 14 Jan 2003

A:K = 11 columns. Change this line accordingly

For iCol = 1 To 11

  cherria 16:55 14 Jan 2003


A curse on you VoG, may your Macros run slow and your sheets fail to recalculate ;-)

I go away for 45 mins to do what I paid to do and you beat me to the solution.

I'd have placed an INDIRECT formula within the invoice itself and then simply updated a counter on the invoice sheet which was referenced in the INDIRECT to pick up a new set of values from the next row.

I'll beat you next time VoG Ah Ha Ha Ha!!!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Samsung Galaxy A8 review: Hands-on

Illustrator Juan Esteban Rodriguez on creating highly detailed official film posters for Star Wars…

iMac Pro review

Comment savoir si quelqu'un a bloqué votre numéro de téléphone ?