Good Morning, I need some help on the following formula for an Invoice List in which I have in one cell the following formula =IF(AND(B26="Carbon"),"59.00",IF(AND(B26="Alloy"),"89.00")) but then in another cell where I have =SUM(F32:F36) in which it would take the amounts from above and added them all together but this doesn't work with that IF Formula or should i use something else instead of the IF statement? I then have other formulas for SUM data to add VAT and the Total amount which needs to work in each stage for my invoice that I am creating. ThanksAdam

Adam- Try using the numbers without the quotes. The quotes are converting the numbers to text. Here is an example image as well as an example file for your reference.

Adam- Please see the below comments 1. You can't complete item 1 without using macros. I have made a custom format for your invoices and taken screenshots in the attached file for your reference. You will also need to put the below code in the workbook code module. YOu can do this by doing the following: a. Use Alt + F11 to access the Visual Basic Editor (VBE) b. Once in the VBE you will need to look to the left for the ThisWorkbook icon c. Double Click the icon d. Paste this code Private Sub Workbook_BeforePrint(Cancel As Boolean) 'Declarations Dim Lrow As Integer 'You will need to change these references according to your setup Sheets("Sheet1").Range("F4") = Sheets("Sheet1").Range("F4") + 1 'Define last row on list worksheet Lrow = Sheets("List").Cells(Sheets("List").Cells.Rows.Count, "A").End(xlUp).Row 'Add New Invoice Number to list Sheets("List").Range("A" & Lrow + 1) = Sheets("Sheet1").Range("F4") End Sub 2. See the formula in the attached spreadsheet.3. I have created a worksheet called List. When the BeforePrint Event runs it will automatically update the list with the new Invoice Number. This may not fit your needs exactly. It's hard for me to give more advice without more knowledge about your file and what Lists you are referring to... See attached

Yes. You need to change the references in the code so they are applicable for your workbook. i.e. Sheets("Sheet1").Range("F4") would need to be changes as follows: Sheets(YOURWORKSHEET).Range(YOURRANGE) The references will need to be changed in all portions of code that are not consistent with your file. I would reccomend trying the code out in the workbook I provided seeing as programmatic changes to workbooks are irreversible.

You can't attach macro enabled files due to security risks. If you attach an .xlsx I can add the code and post it back to the community. If your having trouble attaching an .xlsx I would suggest posting it to a file sharing website and providing a link.

Adam- 1. Delete the Button1 Code Module 2. Delete all code in the Process Invoices Code Module 3. Paste this code in the Process Invoices Code Module Sub Process_Invoices() Dim Lrow As Integer 'Print Worksheet ActiveSheet.PrintOut 'Define Last Row of Invoice List Lrow = Sheets("Invoice List").Cells(Sheets("Invoice List").Cells.Rows.Count, "A").End(xlUp).Row 'Put Invoice value on Invoice List Sheets("Invoice List").Range("A" & Lrow + 1) = Sheets("Invoice").Range("F4").Value 'Increment Invoice Sheets("Invoice").Range("F4") = Sheets("Invoice").Range("F4") + 1 'Put Invoice associated values on Invoice List Sheets("Invoice List").Range("B" & Lrow + 1) = Sheets("Invoice").Range("F6").Value 'Date Sheets("Invoice List").Range("C" & Lrow + 1) = Sheets("Invoice").Range("E15").Value 'Sample ID Sheets("Invoice List").Range("D" & Lrow + 1) = Sheets("Invoice").Range("B15").Value 'Company Name Sheets("Invoice List").Range("E" & Lrow + 1) = Sheets("Invoice").Range("F33").Value 'Invoice Amount Sheets("Invoice List").Range("F" & Lrow + 1) = Sheets("Invoice").Range("F34").Value 'VAT Sheets("Invoice List").Range("G" & Lrow + 1) = Sheets("Invoice").Range("F35").Value 'Total Charge End Sub 4. Right Click The Print Button 5. Select Assign Macro 6. Select Process_Invoices Procedure 7. Now your button will do both

Need help with formula | Microsoft Community Hub

34 Replies

Matt Mickle
Bronze Contributor
Apr 30, 2018
Adam-

Try using the numbers without the quotes. The quotes are converting the numbers to text. Here is an example image as well as an example file for your reference.

AlloyCarbonExampleFormulas.xlsx15 KB
- Adam Jalil
  Brass Contributor
  May 01, 2018
  Morning Matt,
  
  Thank you for coming back to me on the formula, as requested I have attached the document which I need to work on where I have couple of issues
  
  1) The Invoice Tab Cell F4, I need to create a unique Invoice number which will change everytime I press Print it creates a new Invoice Number
  
  2) Cell F25 is where I want either £59 or £89 when in cell B25 has either Carbon or Alloy
  
  3) finally I need the Invoice List tab to work with the data from the Invoice Tab by adding them into a list automatically in all those relevant fields.
  
  Thanks
  Adam
  Invoice Test.xlsx47 KB
  - Matt Mickle
    Bronze Contributor
    May 01, 2018
    Adam-
    
    Please see the below comments
    
    1. You can't complete item 1 without using macros. I have made a custom format for your invoices and taken screenshots in the attached file for your reference. You will also need to put the below code in the workbook code module. YOu can do this by doing the following:
    
    a. Use Alt + F11 to access the Visual Basic Editor (VBE)
    
    b. Once in the VBE you will need to look to the left for the ThisWorkbook icon
    
    c. Double Click the icon
    
    d. Paste this code
    
    Private Sub Workbook_BeforePrint(Cancel As Boolean) 'Declarations Dim Lrow As Integer 'You will need to change these references according to your setup Sheets("Sheet1").Range("F4") = Sheets("Sheet1").Range("F4") + 1 'Define last row on list worksheet Lrow = Sheets("List").Cells(Sheets("List").Cells.Rows.Count, "A").End(xlUp).Row 'Add New Invoice Number to list Sheets("List").Range("A" & Lrow + 1) = Sheets("Sheet1").Range("F4") End Sub
    
    2. See the formula in the attached spreadsheet.
    3. I have created a worksheet called List. When the BeforePrint Event runs it will automatically update the list with the new Invoice Number. This may not fit your needs exactly. It's hard for me to give more advice without more knowledge about your file and what Lists you are referring to...
    
    See attached
    
    InvoiceFile.xlsx51 KB
Damien_Rosario
Silver Contributor
Apr 30, 2018
Hi Adam

It may help to see your spreadsheet or a sample of it.

Are you able to upload it for review?

Cheers
Damien

Forum Discussion

Need help with formula

34 Replies

Resources