Maruf Islam

Greetings Jessica,

Use the SUMIFS function to impose multiple criteria like Product Category, Product, and Dates to find out the total sales of products (i.e., Chocolate Chip or Carrot).

For Chocolate Chips =SUMIFS(G4:G17,E4:E17,J4,F4:F17,J5,B4:B17,">="&J6,B4:B17,"<="&J7)

For Carrot =SUMIFS(G4:G17,E4:E17,M4,F4:F17,M5,B4:B17,">="&M6,B4:B17,"<="&M7)

You can also add all the different sales afterward. Hope, this may help you.

Regards
Maruf Islam (Exceldemy Team)

Greetings Dr. Linda Vandergriff,

Go through the following methods to get rid of the inconvenience. Make sure the value cells are in the Currency or Accounting Number Format.

1. Manually delete the preceding Apostrophes. Or

2. Use a formula to auto remove the Apostrophes as shown in the image.

=NUMBERVALUE(SUBSTITUTE(B2,"'",""))

Feel free to comment if you have further inquiries. We are here to help.

Regards
Maruf Islam (Exceldemy Team)

Greetings Ganesh,

You can find the delivery date using SUM or a simple Arithmetic Formula, as I used in the following picture.

=SUM(B3,7*C3 or D3)
or
=B8+7*C8 or D8

I hope this solves your seeking. Comment, if you need further assistance.

Regards
Maruf Islam (Exceldemy Team)

Greetings Julian Chen,

Sadly, the HYPERLINK function doesn’t support any attachment links in its arguments. You have to use Other Means to attach files. You can use Method 3 of this article to include an attachment.

Regards,
Md. Maruf Islam (Exceldemy Team)

Greetings Tom,

You are right. You can calculate the Total Paid or Payable Amount of a loan using PMT*nper or Cumulative Interest + Loan Amount. And the amount will be the same.
Make sure you enter the Start_period (i.e., minimum 1) and End_period (i.e., within the Total Periods (60)) correctly.

Regards,
Md. Maruf Islam (Exceldemy Team)

Greetings Mani,

Use the following macro code to copy data (from tables or normal ranges) to the bottom of an existing table (i.e., Table1 or anything).
The macro fetches an input box to select the preferred range to copy and paste afterward. Change the Worksheet Name (i.e., vba) and Table Name (i.e., Table1) as existing table.

Sub SelectedDatatoBottom()
Dim ExistingTable As Object
Dim mTable As Range
On Error Resume Next
  Set mTable = Application.InputBox( _
      Title:="Copy Data to Paste to Bottom", _
      Prompt:="Select Cells to Copy", _
      Type:=8)
  On Error GoTo 0
Set ExistingTable = Worksheets("vba").ListObjects("Table1").ListRows.Add
mTable.Copy
ExistingTable.Range.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Worksheets("vba").Activate
End Sub

Download the Excel file for your better assistance.

https://www.exceldemy.com/wp-content/uploads/2022/09/Macro_to_Copy_Data_to_Bottom.xlsm

Regards
Maruf Islam (Exceldemy Team)

Greetings Joseph Clovis,

The AND and FILTER functions do take cell references to filter and fetch entries that fall under the condition (Date>= T0DAY()-30 <TODAY()) respectively. However, sadly, the Excel Filter feature doesn’t support selecting dates from ranges or entries. All the Filter execution does is that it hides rows, which may lead to inconveniences sometimes. Till now, there has been no alternative to selecting dates from the Filter dialog box options. But you can try Conditional Formatting or VLOOKUP function for highlighting and fetching data. And in those cases, you can use cell references from your dataset.

1. Conditional Formatting: Highlight Cells or Range > Go to Home > Conditional Formatting > New Rule > Select a Rule Type and Format > OK.

=AND($B5>=(TODAY()-30),$B5<TODAY())

2. VLOOKUP Function: Use TODAY()-30 in any cell, then use the VLOOKUP formula to fetch different entries. In that way, the dataset range remains intact.

=TODAY()-30
=VLOOKUP(F5,$B$5:$D$16,2,0)
=VLOOKUP(F5,$B$5:$D$16,3,0)

Hope, these ways help you to compensate the Excel Filter caveats. Feel free to comment if the solution doesn’t satisfy your seeking. Our Exceldemy Team is always there to help.

Hi Taylor,

# The macro contains conditions against the email sending within a Week prior to the Deadline/Due Date using the following line

If CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 0 Then
xValSendRng = XRcptsEmail.Offset(k - 1).Value

# So, the email will get sent within- 1 day <= Deadline/Due Date <= 7 day or 1 week range prior to the Deadline/Due Date.

Hope, you find your answer, let us know if further explanation is needed. Our dedicated Softeko Team is always there to help.

Hlw Melissa,

I’m assuming you need to Data Validate multiple selection G7 to H15 (adjacent columns) or whatever the cell is.

# Modify the 1st IF Condition as ” If Not Intersect(Target, Range(“G7:H15″)) Is Nothing Then ” or Change the ” Range(“G7:H15″) ” portion with Capital Letters. Of course don’t forget to apply Data Validation to those cells first.

Hope this works. Otherwise, comment with detailed cell references.

Regards
Maruf

Hlw Reena Pujari. Sorry for late reply.

To apply macro to all cells in the same column, follow:

1. Apply “Data Validation” to all the cell of a column (Highlight Entire Column > go to “Data” > apply “Data Validation”)

2. Replace ” If Target.Address = “$D$4″ Then ” line within the macro (with repetition or without repetition) with ” If Not Intersect(Target, Range(“D:D”)) Is Nothing Then “.

Thanks for your appreciation @Ed