Excel: Need formula to forward to from one worksheet to another 1

[russell123]

Specifically, I have a few columns in one worksheet that include quantity, description, price plus some other columns. But these cells are the most important.

The next worksheet is a standard invoice sheet. I would like to automate that if a quantity appears in the previous worksheet that these three cells above are copied to specific cells in current invoice worksheet, thereby 'not' copying rows where there is no quantity to not unneccessarily populating the invoice sheet.

can this be done in excel?

with great respect for programmers,

Russell

 

[Mikeb1970]

Hi Russel,

This can be done, try this

I'm using Sheet1 for the data (where quantity,description and price are)
Cell A1 will show quantity
Cell B1 will show description
Cell C1 will show price

The formulas should be in Sheet2 (invoice sheet)

formula for Cell A1

=IF(Sheet1!A1<>&quot;&quot;;Sheet1!A1;&quot;&quot

formula for Cell B1

=IF(Sheet1!A1<>&quot;&quot;;Sheet1!B1;&quot;&quot

formula for Cell C1

=IF(Sheet1!A1<>&quot;&quot;;Sheet1!C1;&quot;&quot

This will copy the quantity, description and price to your invoice sheet only when there is a quantity entered in sheet1, thus avoiding copying when there is no quantity sold

Hope this will help you get started

With regards

Mike

 

[SkipVought]

Russell

If you are just looking for 1 row of data to be &quot;copied&quot; to your invoice, this is simply a lookup function.

One of the values would be a key, like Description, In fact, the column in your Invoice that has the &quot;copied&quot; Description, could be a Data/Validation - List, such that when you select a cell in the Description range in the Invoice, a Combo Box pops up with a list of all your Description in the source data sheet. The only careat is that to be able to do that, your source data ranges need to be named. (faq68-1331 How can I rename a table as it changes size)

Then use the selected description as the lookup key to find the associated data

=index(Price,match(B5,Description,0),1)

where B5 is the selected description and Proce & Description are named ranges from your source data.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[CandyS]

Great question and great solutions--thanks guys!

Regards,

Candy

 

[russell123]

Thanks everyone! We're almost there. Mike came the closest (I think) to what I want to acheive. The only addition I need is that if on the source sheet there is no quantity that it not be included on the invoice as an empty row. And that the next row that has a quantity on the source sheet be imputed on the subsequent row in the invoice sheet so that I only see a continuity of items ordered.

It sounds convoluted to me. I hope it is clear.

my best,

Russell

 

[MisterC]

Hi Russell,
If this is an ongoing thing, my suggestion would be to code it.
The code would loop through the cells on the &quot;quantity&quot; sheet. If it found a quantity > 0, it would paste the info to the invoice sheet, otherwise it would go to the next row.
If this sounds in line with what you're looking for, I can post some code to help you out.

Good luck!

 

[russell123]

MisterC,

That's it. I think you have the answer that may resolve this issue.

I would be very grateful if you would post me the code.

Tks,

Russell

 

[MisterC]

OK, a simplified example - many assumptions here. Just cut/paste it into a VBA Module on your spreadsheet.

I've assumed:
The &quot;input area&quot; is on Sheet 1 and starts on row 4 running to row 100.
The &quot;Invoice&quot; is on Sheet 2 and you want to start the items on row 4.
Quantity, Description, & Price are in columns B,C,D

See if you can work through this...

Sub CreateInvoice()
    Dim InputRow        As Integer
    Dim InvoiceRow      As Integer
    
    InvoiceRow = 4   'Set the start row for the invoice info
    
    Application.ScreenUpdating = False
    Worksheets("Sheet1").Activate
    
    'SET THE INPUT ROW RANGE HERE:
    For InputRow = 3 To 100
    
        If ActiveSheet.Cells(InputRow, 2).Value > 0 Then
        'Qty is greater than 0, copy to invoice
        'Select the current row, columns B,C,&D

           ActiveSheet.Range(ActiveSheet.Cells(InputRow, 2), _
                             ActiveSheet.Cells(InputRow, 4)).Select
           Selection.Copy
           Worksheets("Sheet2").Activate
           ActiveSheet.Cells(InvoiceRow, 2).Select
           ActiveSheet.Paste
           Worksheets("Sheet1").Activate
            'Increment the invoice row pointer
            InvoiceRow = InvoiceRow + 1
        End If
    Next InputRow
    Application.ScreenUpdating = True
End Sub

Maybe this will get you close, Good Luck!

 

[MisterC]

Oh, and you'll want to clear the &quot;invoice&quot; range before you do the copying...

 

[russell123]

MisterC,

Your timing is excellent!

 

[russell123]

MisterC,

I've changed the assumptions to run from 6-200 (that is on Sheet1) I hope that is correct
I want it to start on row 19 on sheet2(Invoice)
And I've changed the columns to F,A,D &G

However on the output(invoice(sheet2)) It starts on row 6, and the colums are still B,C &D

So obviously I'm not getting something.


Sub CreateInvoice()
Dim InputRow As Integer
Dim InvoiceRow As Integer

InvoiceRow = 19 'Set the start row for the invoice info

Application.ScreenUpdating = False
Worksheets(&quot;Order&quot.Activate

'SET THE INPUT ROW RANGE HERE:
For InputRow = 6 To 200

If ActiveSheet.Cells(InputRow, 2).Value > 0 Then
'Qty is greater than 0, copy to invoice
'Select the current row, columns F,A,D,&G

ActiveSheet.Range(ActiveSheet.Cells(InputRow, 2), _
ActiveSheet.Cells(InputRow, 4)).Select
Selection.Copy
Worksheets(&quot;Invoice&quot.Activate
ActiveSheet.Cells(InvoiceRow, 2).Select
ActiveSheet.Paste
Worksheets(&quot;Order&quot.Activate
'Increment the invoice row pointer
InvoiceRow = InvoiceRow + 1
End If
Next InputRow
Application.ScreenUpdating = True
End Sub

 

[russell123]

And it's listing all the rows, there is no selection of quantity >0 then input...

 

[russell123]

Maybe I didn't clear it properly 1st???

 

[MisterC]

Hmmm.. You are copying 3 values (from columns B,C,&D) and trying to paste 4?? (To non-adjacent cells even, sheese!!)

Sub CreateInvoice()
    Dim InputRow        As Integer
    Dim InvoiceRow      As Integer
    Dim BCell, CCell, DCell
    
    InvoiceRow = 19   'Set the start row for the invoice info
    Application.ScreenUpdating = False
    Worksheets("Order").Activate
    
    'SET THE INPUT ROW RANGE HERE:
    For InputRow = 6 To 200
    
        If ActiveSheet.Cells(InputRow, 2).Value > 0 Then
        'Qty is greater than 0, copy to invoice
        'Select the current row, columns F,A,D,&G


          BCell = ActiveSheet.Cells(InputRow, 2).Value
          CCell = ActiveSheet.Cells(InputRow, 3).Value
          DCell = ActiveSheet.Cells(InputRow, 4).Value

        Worksheets("Invoice").Activate
'Substitute the 2,3, & 4 with the columns you want:
          ActiveSheet.Cells(InvoiceRow, 2).formula = BCell
          ActiveSheet.Cells(InvoiceRow, 3).formula = CCell
          ActiveSheet.Cells(InvoiceRow, 4).formula = DCell

        Worksheets("Order").Activate

     'Increment the invoice row pointer
            InvoiceRow = InvoiceRow + 1
        End If
    Next InputRow
    Application.ScreenUpdating = True
End Sub

Don't forget to clear the invoice data range first. You may want to use something like

Worksheets("Invoice").Range(Worksheets("Invoice").Cells(1,19),Worksheets("Invoice").Cells(6,100)).Clear
'That will clear cells A19 to F100

 

[MisterC]

Oh, just read your other message.
It is checking the value in the B column for a quantity > 0. If the quantity isn't in the B column, you'll need to change that reference.

If ActiveSheet.Cells(InputRow, 2).Value > 0 Then

The .Cells object takes two params: Row & Column. The code sets the row to &quot;InputRow&quot; (the incrementing variable). I've set the column to &quot;2&quot; (Which is column B)

If your quantity is in another column, change the 2 accordingly (3 for col C, 4 for col D, etc...)

Sorry if the code isn't clear, it is kind of a hip shoot.

 

[russell123]

Thank you so much. Just a couple of questions. It's looking better. Please forgive the naive questions.

1. Where do I place the 'clear' code from above? In the same macro?

2. Can I use this macro in the 'Invoice' template or do I have to start fresh?

 

[russell123]

I ask the 2nd question because it seems to work better when it's a fresh worksheet.

 

[MisterC]

No Problem.
The &quot;Clear&quot; code should go in the same sub. You can put it right after the Application.ScreenUpdating = False line.

Yes you can use the code in the Invoice template. If you have placed it in a VBA Module, you can easily assign the code to the &quot;click&quot; event of a button you put in your spreadsheet. There is no need to &quot;start fresh&quot;; the spreadsheet should be completely re-usable.

 

[russell123]

MisterC et al,

Thank you for the code. It works beautifully. 2 questions remain.

1. Can this macro run automatically when there is input or do I have to say 'run macro' or by using the shortcut key?

2. More importantly, the code runs a subtotal and total calc. Is there a way to remove this because I already have it in the worksheet in the appropriate place with various calculations?

Here's what I have so far:


Sub CreateInvoice()
Dim InputRow As Integer
Dim InvoiceRow As Integer
Dim BCell, CCell, DCell

InvoiceRow = 17 'Set the start row for the invoice info
Application.ScreenUpdating = False
Worksheets(&quot;Invoice&quot.Range(Worksheets(&quot;Invoice&quot.Cells(1, 17), Worksheets(&quot;Invoice&quot.Cells(5, 100)).Clear
'That will clear cells A17 to E100

Worksheets(&quot;Order&quot.Activate

'SET THE INPUT ROW RANGE HERE:
For InputRow = 6 To 200

If ActiveSheet.Cells(InputRow, 6).Value > 0 Then
'Qty is greater than 0, copy to invoice
'Select the current row, columns F,A,D,&G


BCell = ActiveSheet.Cells(InputRow, 6).Value
CCell = ActiveSheet.Cells(InputRow, 1).Value
DCell = ActiveSheet.Cells(InputRow, 4).Value
ECell = ActiveSheet.Cells(InputRow, 7).Value

Worksheets(&quot;Invoice&quot.Activate
'Substitute the 2,3, & 4 with the columns you want:
ActiveSheet.Cells(InvoiceRow, 2).Formula = BCell
ActiveSheet.Cells(InvoiceRow, 3).Formula = CCell
ActiveSheet.Cells(InvoiceRow, 4).Formula = DCell
ActiveSheet.Cells(InvoiceRow, 5).Formula = ECell

Worksheets(&quot;Order&quot.Activate

'Increment the invoice row pointer
InvoiceRow = InvoiceRow + 1
End If
Next InputRow
Application.ScreenUpdating = True
End Sub

 

[MisterC]

> 1. Can this macro run automatically when there is input or do I have to say 'run macro' or by using the shortcut key?

LOL - Sure, it could run whenever there is input. Maybe the best way to accomplish what you are after is to call the code from the Invoice sheet's Activate event. Then it will run whenever you change over to the Invoice sheet. You may want to flash a message box that the invoice is refreshing to let the user know what's going on.

>2. More importantly, the code runs a subtotal and total calc. Is there a way to remove this because I already have it in the worksheet in the appropriate place with various calculations?

Actually, there is no total or subtotal calculation in the code. Where exactly is the subtotal & total calc coming up? Do you have subtotals & totals on your &quot;Order&quot; sheet? Uh-oh if so. You'll have to change the way the loop processes its values.