Forum Discussion

Adam Jalil's avatar
Adam Jalil
Brass Contributor
Apr 30, 2018

Need help with formula

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...
Formulas & Functions
training
Matt Mickle's avatar
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.

 

  • Adam Jalil's avatar
    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

    • Matt Mickle's avatar
      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

       

      • Adam Jalil's avatar
        Adam Jalil
        Brass Contributor
        May 01, 2018
        Should the Sheets be renamed as Invoice or "List" be renamed as Invoice List?

Resources