Copy worksheet by formula
Closed
jepun
-
Jun 15, 2010 at 05:47 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 26, 2010 at 11:51 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 26, 2010 at 11:51 AM
Related:
- To preserve the original version of a workbook so you can make changes to a copy of it, which of the following would you do?
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Transfer data from one excel worksheet to another automatically - Guide
- Excel grade formula - Guide
- Number to words in excel formula - Guide
- Date formula in excel dd/mm/yyyy - Guide
4 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 15, 2010 at 07:54 PM
Jun 15, 2010 at 07:54 PM
hard to see your data here. Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
thank u for ur reply. i already upload the workbook as u can see below
https://authentification.site/files/23026821/Book2.xls
as u can see frm the workbook, i have 2 sheet in it.
1) Daily a/c due - key in all the invoice that cust make early payment in that current month (in this case is in June) and date when exactly the invoice is due (under due date column).
while the "entry date" colum indicating when the payment rec'd from cust
2) Monthly Reconcile a/c - summary of invoice that not yet due but cust already make early payment on it. this data come from "daily a/c due" sheet
i really hope there is a way to make:
1) to transfer account details that not yet due to worksheet "Monthly Reconcile" by using excel function / formula only. (I don't know how to use VBA code / macro), such as by using IF function
2) i have use conditional formating on column D in "Daily a/c due" worksheet, to remind me the due a/c on next day, but it only highlight the colum D.
How can i make it highlight all the row that due on the next day.
Thank you
https://authentification.site/files/23026821/Book2.xls
as u can see frm the workbook, i have 2 sheet in it.
1) Daily a/c due - key in all the invoice that cust make early payment in that current month (in this case is in June) and date when exactly the invoice is due (under due date column).
while the "entry date" colum indicating when the payment rec'd from cust
2) Monthly Reconcile a/c - summary of invoice that not yet due but cust already make early payment on it. this data come from "daily a/c due" sheet
i really hope there is a way to make:
1) to transfer account details that not yet due to worksheet "Monthly Reconcile" by using excel function / formula only. (I don't know how to use VBA code / macro), such as by using IF function
2) i have use conditional formating on column D in "Daily a/c due" worksheet, to remind me the due a/c on next day, but it only highlight the colum D.
How can i make it highlight all the row that due on the next day.
Thank you
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 18, 2010 at 10:03 PM
Jun 18, 2010 at 10:03 PM
Ok first answer to your two question
#1: you have to use macro
#2: keep your formula as before but select the rows. For example select all rows and the go to conditional formatting and apply the =INT($D:$D)=TODAY() By using $ sign you force the evaluation of condition based on column D for every cell
Now to my confusion. I am confused over your book and explanation.
take #2.
You say that "Monthly Reconcile a/c " shows those records for which the invoice is not due and yet customer makes a payment. On that sheet you show two rows for ref# 9 and 10 for amount 230 and 269. This payment was made on June 3. Now if I look at the detail sheet, I see that for #9 there is a date but for #10 there is not any date. Also I see the for #1 there is a date yet it does not show on your summary sheet. Could you go more into detail to break down the how data show up on the other sheeet
#1: you have to use macro
#2: keep your formula as before but select the rows. For example select all rows and the go to conditional formatting and apply the =INT($D:$D)=TODAY() By using $ sign you force the evaluation of condition based on column D for every cell
Now to my confusion. I am confused over your book and explanation.
take #2.
You say that "Monthly Reconcile a/c " shows those records for which the invoice is not due and yet customer makes a payment. On that sheet you show two rows for ref# 9 and 10 for amount 230 and 269. This payment was made on June 3. Now if I look at the detail sheet, I see that for #9 there is a date but for #10 there is not any date. Also I see the for #1 there is a date yet it does not show on your summary sheet. Could you go more into detail to break down the how data show up on the other sheeet
In my "daily a/c" worksheet i have this column:
Entry Date - the date when we received the early payment from cust
Cust Name - the name of cust that make early payment
Ref No - the invoice ref no.
Due Date - the due date for the invoice to be paid
Amt Rec'd - the amount we received from cust for early payment
Amt Rev'd - the amount we reversed back / contra in our system upon the due date
In my "Monthly Reconcile" worksheet i have this column:
Date Of Entry - the date when we received the early payment from cust
Cust Name - the name of cust that make early payment
Ref No. - the invoice ref no.
Remarks/ Due Date - the due date for the invoice to be due for payment
Amount Due - the amount that still pending for reversal / contra in our system upon due date
For the two rows under Ref no.: 9 & 10 ( Amount 230 & 269), explaination as below:
Entry Date - 3 - June - 2010 (the date when we received the early payment from cust)
Cust Name - ABC (the name of cust that make early payment)
Ref No - No. 9 & 10 (the invoice ref no.)
Due Date - 30-June-2010 & 9-June-2010 (the actual date where the invoice should be paid - where cust should paid the inv on 30-june & 9-june but cust make an early payment on 03-june)
Amt Rec'd - 230 & 269 (the amount we received from cust for early payment of the inv)
_______________________________________________________________________
For Ref no.: 1, the explaination as follows:
Entry Date - 01-june-2010
Cust Name - ABC
Ref No - No. 1
Due Date - 03-june-2010
Amt Rec'd - 251
As u can see, on 1-june, we rec'd payment from ABC to pay their invoice no.1 amount 251.
The invoice should be paid on 03-june, but cust make an early payment for their inv no.: 1
On 1 June, i record every details in the worksheet "Daily AC Due"
On 03-june, where the invoice no.1 due, i contra the amount 251 under amount rev'd column.
So i key in the relevant details on 03-June for inv no.: 1,
where u can see on 03-June, the amount 251 is for inv no.1 which has been reversed out.
Amt Rev'd - 251 (the amount we reversed back / contra in our system upon the due date )
Contra here means: We rec'd payment from cust for inv no.1, due to our system, we can only set off the amount to the goods they bought upon maturity of the invoice.
When i put the amount under "Amt Rev'd" column, meaning that the money already set off / contra to the goods / product cust ABC bought.
_______________________________________________________________________
Regarding the date under "Entry date" column in both worksheet, you can refer to the new workbook 2 i upload here : https://authentification.site/files/23042016/Book2.xls
_______________________________________________________________________
1) The question :
How i want to transfer the invoice data that not yet been reversed out or due, to the "Monthly Reconcile" worksheet without using macro.
I don't know to use macro or write macro using the vba, so how i want to google it. i don't have any clue regarding macro
thanks
Entry Date - the date when we received the early payment from cust
Cust Name - the name of cust that make early payment
Ref No - the invoice ref no.
Due Date - the due date for the invoice to be paid
Amt Rec'd - the amount we received from cust for early payment
Amt Rev'd - the amount we reversed back / contra in our system upon the due date
In my "Monthly Reconcile" worksheet i have this column:
Date Of Entry - the date when we received the early payment from cust
Cust Name - the name of cust that make early payment
Ref No. - the invoice ref no.
Remarks/ Due Date - the due date for the invoice to be due for payment
Amount Due - the amount that still pending for reversal / contra in our system upon due date
For the two rows under Ref no.: 9 & 10 ( Amount 230 & 269), explaination as below:
Entry Date - 3 - June - 2010 (the date when we received the early payment from cust)
Cust Name - ABC (the name of cust that make early payment)
Ref No - No. 9 & 10 (the invoice ref no.)
Due Date - 30-June-2010 & 9-June-2010 (the actual date where the invoice should be paid - where cust should paid the inv on 30-june & 9-june but cust make an early payment on 03-june)
Amt Rec'd - 230 & 269 (the amount we received from cust for early payment of the inv)
_______________________________________________________________________
For Ref no.: 1, the explaination as follows:
Entry Date - 01-june-2010
Cust Name - ABC
Ref No - No. 1
Due Date - 03-june-2010
Amt Rec'd - 251
As u can see, on 1-june, we rec'd payment from ABC to pay their invoice no.1 amount 251.
The invoice should be paid on 03-june, but cust make an early payment for their inv no.: 1
On 1 June, i record every details in the worksheet "Daily AC Due"
On 03-june, where the invoice no.1 due, i contra the amount 251 under amount rev'd column.
So i key in the relevant details on 03-June for inv no.: 1,
where u can see on 03-June, the amount 251 is for inv no.1 which has been reversed out.
Amt Rev'd - 251 (the amount we reversed back / contra in our system upon the due date )
Contra here means: We rec'd payment from cust for inv no.1, due to our system, we can only set off the amount to the goods they bought upon maturity of the invoice.
When i put the amount under "Amt Rev'd" column, meaning that the money already set off / contra to the goods / product cust ABC bought.
_______________________________________________________________________
Regarding the date under "Entry date" column in both worksheet, you can refer to the new workbook 2 i upload here : https://authentification.site/files/23042016/Book2.xls
_______________________________________________________________________
1) The question :
How i want to transfer the invoice data that not yet been reversed out or due, to the "Monthly Reconcile" worksheet without using macro.
I don't know to use macro or write macro using the vba, so how i want to google it. i don't have any clue regarding macro
thanks
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 20, 2010 at 06:16 AM
Jun 20, 2010 at 06:16 AM
A little clear but still some confusion
on your new book, row 4
Entry Date: 01-Jun-10
Cust Name: ABC
Ref No: 1
Due Date 3-Jun-10
Amt Rec'd: 251.00
Amt Rev'd
Total
on your new book, row 14
Entry Date: 03-Jun-10
Cust Name: ABC
Ref No: 9
Due Date 30-Jun-10
Amt Rec'd: 230.00
Amt Rev'd
Total
Just looking at these two rows, since decision is to be made by Entry Data, the row data says both should appears. I understand that you have a row at 17 too for row#4. But how it fits into your design of if entry date < due date, then the payment was made early.
About reversing the amount, lets say row 14 read as ( i have changed due date)
Entry Date: 03-Jun-10
Cust Name: ABC
Ref No: 9
Due Date 15-Jun-10
Amt Rec'd: 230.00
Amt Rev'd
So on june 3,till june 14 this row is valid row to be shown on Monthly Reconcile
now on june 16, how this row would be reversed from "Monthly Reconcile" ??
on your new book, row 4
Entry Date: 01-Jun-10
Cust Name: ABC
Ref No: 1
Due Date 3-Jun-10
Amt Rec'd: 251.00
Amt Rev'd
Total
on your new book, row 14
Entry Date: 03-Jun-10
Cust Name: ABC
Ref No: 9
Due Date 30-Jun-10
Amt Rec'd: 230.00
Amt Rev'd
Total
Just looking at these two rows, since decision is to be made by Entry Data, the row data says both should appears. I understand that you have a row at 17 too for row#4. But how it fits into your design of if entry date < due date, then the payment was made early.
About reversing the amount, lets say row 14 read as ( i have changed due date)
Entry Date: 03-Jun-10
Cust Name: ABC
Ref No: 9
Due Date 15-Jun-10
Amt Rec'd: 230.00
Amt Rev'd
So on june 3,till june 14 this row is valid row to be shown on Monthly Reconcile
now on june 16, how this row would be reversed from "Monthly Reconcile" ??
I will update in "Daily AC Due" worksheet at 15-Jun-10 as below :
Entry Date: 15-Jun-10
Cust Name: ABC
Ref No: 9
Amt Rev'd : 230
And, on June 15 (i monitor the workbook on daily basis, so i will reversed it on 15-June not 16-June), i will deleted this row in "Monthly Reconcile" worksheet
that why i try to configure it auto by using excel function to link these 2 worksheet, if possible...
Thanks
Entry Date: 15-Jun-10
Cust Name: ABC
Ref No: 9
Amt Rev'd : 230
And, on June 15 (i monitor the workbook on daily basis, so i will reversed it on 15-June not 16-June), i will deleted this row in "Monthly Reconcile" worksheet
that why i try to configure it auto by using excel function to link these 2 worksheet, if possible...
Thanks
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 21, 2010 at 09:29 AM
Jun 21, 2010 at 09:29 AM
Ok 2 more questions ( for now).
So are you looking for this
As soon as you type in "entry date", check if entry date is less than today's date and less than due date. If that happens, copy the data from that row to reconcile sheet, provided that ref code does not already exists on monthly recon.
does it matter where the data is copied. I see that you have sorted by date and each day has a row in between.
So are you looking for this
As soon as you type in "entry date", check if entry date is less than today's date and less than due date. If that happens, copy the data from that row to reconcile sheet, provided that ref code does not already exists on monthly recon.
does it matter where the data is copied. I see that you have sorted by date and each day has a row in between.
In the "Monthly Reconcile" worksheet, i already sort it out based on entry date. The row in between just add-up to make the worksheet look nice. Basically the data in monthly reconcile is sort based on entry date and followed by due date.
the thing that i look for is just like what u said, except it make the due date as reference. Meaning that everything that due more than 30/06/2010 (based on this workbook) is consider as pending reversed and will be copied in "Monthly Reconcile" worksheet
thanks
the thing that i look for is just like what u said, except it make the due date as reference. Meaning that everything that due more than 30/06/2010 (based on this workbook) is consider as pending reversed and will be copied in "Monthly Reconcile" worksheet
thanks
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 26, 2010 at 11:51 AM
Jun 26, 2010 at 11:51 AM
Ok Try this. This will recreate the data rows in the recon sheet.
Sub ReCreateRecon()
Dim lMaxRows As Long
Dim sTempSheet As String
Dim sDaily As String
Dim sRecon As String
Dim lDailyStartRow As Long
Dim lReconStartRow As Long
Dim lReconNewRow As Long
sTempSheet = "my_temp_sheet"
sDaily = "Daily AC Due"
sRecon = "Monthly Reconcile"
lRecConStartRow = 14
lDailyStartRow = 2
Sheets(sDaily).Select
ActiveSheet.AutoFilterMode = False
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(lDailyStartRow, "A"), Cells(lMaxRows, "G")).Select
Selection.AutoFilter FIELD:=1, Criteria1:="<>"
Selection.AutoFilter FIELD:=4, Criteria1:=">" & Date
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
If (lMaxRows < lDailyStartRow + 1) Then GoTo End_Sub
On Error Resume Next
Application.DisplayAlerts = False
Sheets(sTempSheet).Delete
Application.DisplayAlerts = True
Err.Clear
On Error GoTo Error_Handle
Sheets.Add
ActiveSheet.Name = sTempSheet
Sheets(sDaily).Select
Range(Cells(lDailyStartRow, "A"), Cells(lMaxRows, "G")).Copy
Sheets(sTempSheet).Select
Range("A1").PasteSpecial
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(1, "A"), Cells(lMaxRows, "G")).Sort _
Key1:=Range("A2"), order1:=xlAscending, _
Key2:=Range("C2"), order2:=xlAscending, _
Header:=xlYes
lReconNewRow = Cells(Rows.Count, "A").End(xlUp).Row
Sheets(sRecon).Select
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row - 3
Rows(lRecConStartRow & ":" & lMaxRows).ClearContents
Rows(lRecConStartRow + 1 & ":" & lMaxRows - 1).Delete
Sheets(sTempSheet).Select
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(2, "A"), Cells(lMaxRows, "E")).Copy
Sheets(sRecon).Select
Range("A" & lRecConStartRow + 1).Select
Selection.Insert Shift:=xlDown
lMaxRows = lRecConStartRow + 1
Do While (Cells(lMaxRows, "A") <> "")
If (Cells(lMaxRows, "A") <> Cells(lMaxRows + 1, "A")) Then
Rows(lMaxRows + 1).Insert
lMaxRows = lMaxRows + 1
End If
lMaxRows = lMaxRows + 1
Loop
End_Sub:
Sheets(sDaily).Select
ActiveSheet.AutoFilterMode = False
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(lDailyStartRow, "A"), Cells(lMaxRows, "G")).Select
Selection.AutoFilter
Range("A" & lDailyStartRow).Select
On Error Resume Next
Application.DisplayAlerts = False
Sheets(sTempSheet).Delete
Application.DisplayAlerts = True
Err.Clear
On Error GoTo 0
Exit Sub
Error_Handle:
MsgBox Err.Description
GoTo End_Sub
End Sub
