Apr 30 2018
04:17 PM
- last edited on
Jul 25 2018
11:53 AM
by
TechCommunityAP
Apr 30 2018
04:17 PM
- last edited on
Jul 25 2018
11:53 AM
by
TechCommunityAP
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
Apr 30 2018 04:41 PM
Apr 30 2018 04:45 PM
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.
May 01 2018 12:02 AM
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
May 01 2018 06:23 AM
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
May 01 2018 07:08 AM
May 01 2018 07:10 AM
May 01 2018 07:25 AM
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.
May 01 2018 07:32 AM
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
May 01 2018 07:49 AM
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
May 01 2018 08:06 AM - edited May 01 2018 08:07 AM
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.
May 01 2018 08:36 AM
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
May 01 2018 08:46 AM
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.
May 01 2018 08:55 AM
For some reason I am unable to attach my excel file which is non-sensetive
May 01 2018 10:04 AM
May 01 2018 11:32 AM
HI Matt,
Attached is the excel file of the Invoice and Invoice List for you to try it out.
Regards
Adam
May 01 2018 11:37 AM
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
May 01 2018 08:10 PM
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.
May 01 2018 11:04 PM
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