Posts by LCLARK62

I need to hide different rows based on a cell value.

The target cell will always be the same, but there are eight different values in the drop down list in the cell.

I am using Worksheet Change in VBA but it is not working
Note: A user may select a value from the drop down list in the Target Cell, but may then change their mind and select another value.
I need only the rows hidden for the selection chosen in the drop down list.
All other rows should be shown.
Following is a sample of code I am trying to use

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$L$22" Then
        Rows("26:36").Hidden = (Target.Value = "NEW PHONE & MOBILE NUMBER")
        Rows("24:32").Hidden = (Target.Value = "DATACARD FOR LAPTOP")
End If
End Sub

When selecting NEW PHONE & MOBILE NUMBER, rows 26:32 are hidden as expected, but if I then change the selection in the target cell to DATACARD FOR LAPTOP only rows 24:26 are hidden. Rows 27:32 are not hidden

Re: Automate Individual Emails with unique Data as Attachment using Macro

Hi there.

This is exactly what I was looking for. Much appreciated

Regards
Lance

Have approx 2000 Mobile Phone charge records each month. Each Mobile record is assigned a reporting Managers email address.

Each record includes the following data
State
Service Number
Service Name
Calls & Usage Charges
Other Charges or Credits
Service Access Charges
Total Charges Inc. GST
Billing Month
Reporting Line Manager
Name Manager Email

I want to be able to do the following with the monthly data:

For each Managers email id, generate a email containing all the mobile phone charges which belong to the managers email as a spreadsheet.
Also want to be able to include a standard email message in the body of the email.

I have attached a sample file of data.
In the sample file, there are 16 mobile phone records which belong to manager email [email protected]
There are another 12 mobile phone records for manager [email protected]

What I am after is a macro that the automatically generate a email to [email protected] with a spreadsheet containing the 16 records belonging to him and another email to [email protected] with a spreadsheet containing the 12 records belonging to him

Any help with this would be much appreciated

Re: Using SUMIF combined with VLOOKUP

Hi Windy,

thanks for your reply. I know that Pivot Tables could be easliy be used to acheive what I am looking for here, but would like to understand how to manage it using a formula to upskill my knowledge in Excel

Any assistance with this would be appreciated when you have some time.

Regards
Lance

Hi,

I am trying to use SUMIF combined with VLOOKUP to return dat based on mulitple criteria

I have 2 Spreadsheets
Spreadsheet 1 contains the following columns with data. This spreadsheet is called Invoice Register and each month invoices from Customers will be entered into this spreadsheet

Customer Name
Month
Invoice #
Invoice Total

The 2nd Spreadsheet has Customer Name and a seperate column for each month of the year. This spreadsheet is called Running Invoice Totals

I need to build a SUMIF combined with VLOOKUP formula to do the following

Lookup Customer Name in 2nd spreadsheet against Customer Name in 1st spreadsheet
If Customer Name found, then if Month in 1st spreadsheet = MMM ( eg Jul), then return the total sum of all invoices for the customer in the month of Jul

I have attached a file with some sample data.
This is most probably basic stuff, but have had difficulty in finding a correct answer on how to build this type of combination with multiple criteria

Any assistance will be much appreciated

Re: Find Empty Cells in Excel and Prompt User with MsgBox with Macro

Thanks for the feedback and all good now. Also have taken note of the rules when using code in threads.

Regards
Lance

Hi, I have a Excel Form on which I need to run a Macro to check some key cells and if the cells are empty, I want to inform the user which cell is empty and then have the macro go to the empty cell.

There may be multiple empty cells in the form

I have setup the following macro, but it doesn't seem to work. It only finds the first empty cell and then ignores any other empty cells.

Also not sure how to get the macro to return the user to each empty cell for required actions
I have added the macro I have put together below. Any assistance with this would be great.

Sub Check_For_Empty_Fields()
' When user hits ADD RECORD Button, Macro checks for Missing Field Data for required upload file
If Range("J58") = "" Then
MsgBox ("Please fill in Supplier Name in Section 3.")
Exit Sub
ElseIf Range("J60") = "" Then
MsgBox ("Please fill in Street Address 1 in Section 3.")
Exit Sub
ElseIf Range("J62") = "" Then
MsgBox ("Please fill in Street Address 2 in Section 3.")
Exit Sub
ElseIf Range("J64") = "" Then
MsgBox ("Please fill in Suburb in Section 3.")
Exit Sub
ElseIf Range("J66") = "" Then
MsgBox ("Please select State in Section 3.")
Exit Sub
ElseIf Range("P66") = "" Then
MsgBox ("Please fill in Postcode in Section 3.")
Exit Sub
ElseIf Range("Y64") = "" Then
MsgBox ("Please fill in Supplier Email Address in Section 3.")
Exit Sub
ElseIf Range("Y66") = "" Then
MsgBox ("Please fill in Remittance Method in Section 3.")
Exit Sub
ElseIf Range("J72") = "" Then
MsgBox ("Please select in AR Contact Telephone Area Code in Section 3.")
Exit Sub
ElseIf Range("N72") = "" Then
MsgBox ("Please fill in AR Contact Telephone Number in Section 3.")
Exit Sub
ElseIf Range("P72") = "" Then
MsgBox ("Please fill in AR Contact Telephone Number in Section 3.")
Exit Sub
ElseIf Range("J74") = "" Then
MsgBox ("Please select in AR Contact Fax Area Code in Section 3.If Fax Number Not Applicable select (NA) from list")
Exit Sub
ElseIf Range("N74") = "" Then
MsgBox ("Please fill in AR Contact Fax Number in Section 3.If Fax Number Not Applicable enter 0000 into field")
Exit Sub
ElseIf Range("P74") = "" Then
MsgBox ("Please fill in AR Contact Fax Number in Section 3.If Fax Number Not Applicable enter 0000 into field")
Exit Sub
ElseIf Range("D78") = "" Then
MsgBox ("Please fill in ABN number in Section 3.")
Exit Sub
ElseIf Range("J78") = "" Then
MsgBox ("Please fill in ABN number in Section 3.")
Exit Sub
ElseIf Range("F84") = "" Then
MsgBox ("Please fill in Bank BSB Number in Section 3.")
Exit Sub
ElseIf Range("F86") = "" Then
MsgBox ("Please fill in Bank Account Number in Section 3.")
Exit Sub
End If
End Sub

Regards
Lance