Sep 19 2022 07:38 AM
I have an excel spreadsheet that I am doing grouping by rows function, then Subtotal function based off of invoice numbers (column A). In the total rows, I am needing more information carried down from the row above the total column. Is there a way to do this?
Example:
Invoice Number | Client Code | Invoice Date | Total Charges | Total Expenses | Total Amount Due | COMPANY | CUSTOMER | LOCATION | INVC_PREFIX | CUSTOMER NAME |
080522-1411594 | UHC-MR | 44778.59141 | 600 | 56.88 | 656.88 | 66 | 1008 | ATL66 | UR | Example customer name |
080522-1411594 Total | 13875 | 3537.69 | 17412.69 |
I am needing the Company, Customer, Location, and Invc_Prefix to be in the total row as well.
This spreadsheet has over 100 different invoice numbers, so I would need to do this at each row that has the "Total" after the invoice number in Column A.
Thank you for your assistance.
Sep 19 2022 07:52 AM
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Sep 19 2022 08:20 AM
Sep 19 2022 08:29 AM - edited Sep 19 2022 08:30 AM
No, I can't access the file, sorry. It requires a login.
Sep 19 2022 08:41 AM
Sep 19 2022 09:16 AM
That works. I have to do something else now, if there are no replies I'll get back to you later.
Sep 19 2022 11:51 AM
Sub fill()
Dim i As Long
Dim j As Long
j = Range("F" & Rows.Count).End(xlUp).Row
For i = 1 To j
If Cells(i, 7).Value = "" Then
Range(Cells(i, 7), Cells(i, 10)).Value = Range(Cells(i - 1, 7), Cells(i - 1, 10)).Value
Else
End If
Next i
End Sub
You can try these lines of code. In the attached file you can click the button in cell L2 to run the macro.
Sep 19 2022 11:54 AM
@OliverScheurich - Thank you! I will try this out.
Sep 19 2022 12:00 PM
SolutionAlternatively: select G1:J57.
Press Ctrl+G or F5 to activate the Go To dialog.
Click Special...
Select Blanks.
Click OK.
G3 should now be the active cell in the selection.
Enter the formula =G2 and press Ctrl+Enter to propagate the formula to all blank cells in the selection.
Sep 19 2022 12:20 PM
Sep 26 2022 05:44 AM
@Hans Vogelaar Thank you!! This made it very easy!!
Sep 19 2022 12:00 PM
SolutionAlternatively: select G1:J57.
Press Ctrl+G or F5 to activate the Go To dialog.
Click Special...
Select Blanks.
Click OK.
G3 should now be the active cell in the selection.
Enter the formula =G2 and press Ctrl+Enter to propagate the formula to all blank cells in the selection.