Need help with formula

Brass Contributor

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.

 

Thanks

Adam

34 Replies
Hi Adam

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

Are you able to upload it for review?

Cheers
Damien

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.

 

AlloyCarbonIfFormula.png

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

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

 

Hiya,
I cut and pasted your coding as advised but for some reason when I do run or save it comes up with the following error response.

Run-time error "9":
Subscript out of range

Regards
Adam
Should the Sheets be renamed as Invoice or "List" be renamed as Invoice List?

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.

 

This is what I did as per your coding and have attached the word document to show you.

 

Private Sub Workbook_BeforePrint(Cancel As Boolean)
   
    'Declarations
    Dim Lrow As Integer
   
    'You will need to change these references according to your setup
    Sheets("Invoice").Range("F4") = Sheets("Invoice").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("Invoice").Range("F4")
   
End Sub

 

 

Hi Matt,

 

So sorry to disturb you on this matter, I have copied and modified what you advised me and still I am getting an error on the Invoice List and etc... Can you please check and advise me what I am doing wrong..

 

Thank you so much for all the help you have done so far.

 

Kind regards

Adam

Based on what I saw in your screenshot you need to change all instances of:

 

Sheets("List")

to

 

Sheets("Invoice List")

 

The subscript out of range error you were getting basically means that the application can't find the worksheet named "List" that your referencing because it doesn't exist in your workbook.

 

One other note is that the code will only increment the invoice numbers when you print the document.  If you want the Invoices formatted as I have referenced in the file INV0000001 etc.  You will need to change the format of the cells populated on the Invoice List worksheet to match the format I have used in the Invoice worksheet.

Hi Matt,

 

I still have the issue in regards to when I press Print that the data is not going to Invoice List tab even thou I have done all the VBA coding as per your request...

 

Can you have a look and advise me where I am going wrong please.

 

Many thanks

Adam

Please attach a non-sensitive example workbook.  This will help me better understand your data format and allow me to drill in on your specific issue.

Attached is the document as requested Matt

For some reason I am unable to attach my excel file which is non-sensetive

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.

HI Matt,

Attached is the excel file of the Invoice and Invoice List for you to try it out.

 

Regards

Adam

Hi Matt,

 

I have a print macro on the invoice tab which I presume will work when I press that button that it will add the Invoice number in the Invoice List and also the data on the other cells as per the VLOOKUP that is added there...

 

Will the system automatically add it to the next line when I have the next invoice also?

 

Thanks

Adam

The vlookups you have won't work for your purposes.  You need to update the actual values in the table using this amended code.  Please see this example file.  You can work this code into your current macro.  There is no need for a workbook event based on the content of the file I reviewed.

 

Please see this example file: LINK TO EXAMPLE FILE

 

Click the button I have created on the Invoice worksheet a couple of times and then view the results on the Invoice List worksheet.

 

 

 

 

 

Morning Matt,

 

The formula and all looks good thank you :)

 

Does the following need to have he word Test () - Sub Test() or can I remove the word Test from coding?

 

Thanks

Adam