Transferring specific data from a master list to another worksheet specific cell

0

The "Data Input" sheet feeds the "General Ledger" adding the new data in 9 columns to the last row.

I need the information in "General Ledger" (master list) column 7 ( Cell "G", "Net")  entered into a specific cell in ("Year by Month" sheet).

The specific cell is based on the criteria in column 1 (Income & Expense Category and corresponding month (row 3, "Year by Month" sheet.)  

I Have included a sample diagram under the "Example" tab

I'm using the "cash" method. Also, this workbook is only for a calendar year. A copy will be made for future years with updates.

I'm really a newbie willing to learn more and I really don't know what I'm looking for. Thanks!

Notes:

Two files attached:

1.) Micro Buss Books July2021 Beta Working Copy v0 a BU

I could not get it to work

2.) Errors & Flow Micro Buss Books July2021

Contain my notes

Original:

I took the 'Professional Forms in Excel' course and managed to create an entry form that does exactly what I needed. My original plan was to do a monthly search and enter the data manually. After experiencing the power of Excel. I thought I could get it done automaticaly …but I have failed. I'm willing to learn more but I really don't know what I'm looking for. Thanks!

My master list has 9 columns

I need the information in column 7 entered into a specific cell in another worksheet based on the criteria in column 1 & column 8 AND updated when new rows are entered in the "General Ledger" (master list)

ADDITIONAL NOTES Updated Exel file added

Answer
Discuss

Discussion

Martin

Please edit your original question to add an Excel file (/ expand your requirement)- that way we'll have more clues to help you. Dummy data will be fine but the file should be representative of what you're trying to do. Suggest you leave your code in the file- it may be easy to correct.

Kindly say too whether or not new rows in your Master Sheet are at the end (last row) or anywhere in that sheet.
John_Ru (rep: 1557) Jul 21, '21 at 10:58 am
Martin

Thanks for adding file but the associated VBA Project is password protected (so we can't see it). Also is the sheet called "General Ledger" the "Master List" mentioned in your original question?

Again, kindly edit your original question to provide unprotected file and words of explanation.
John_Ru (rep: 1557) Jul 21, '21 at 12:41 pm
I have udated my question by uploading my excel file. The answer to your query "Kindly say too whether or not new rows in your Master Sheet are at the end (last row) or anywhere in that sheet"  new rows are at the end (last row). Please note that "Master Sheet" has been renamed "General Ledger" The destination sheet is "Monthly" I thank you for all your help.
Martin100 (rep: 4) Jul 21, '21 at 12:46 pm
Sorry for the incomplete information. 
all the passwords while working  "123"
The excell file has been updated.
The "Geneal Ledger" tab is the master file
Thank You!
Martin100 (rep: 4) Jul 21, '21 at 1:04 pm
Martin

More clarification needed (but please add to your question, not this discussion).

Are you saying that the Data Input sheet enters values into the sheet General Ledger but you also want the value to be added to the correct cell in the year's expenses sheet based on the chosen Income/Expense caterory (from Data Input) and the month?

I assume the Monthly sheet is for the current calendar year but what will you do at the end of 2021 (especially if some expenses are late or are anticiapted/ need to be accrued)? Have a new, blank file for 2022?

This is a test file but for real expenses, how will you accumulate values in Monthly (given your macro hasn't done that so far)?
John_Ru (rep: 1557) Jul 21, '21 at 1:38 pm
Unfortunately the file attached to your answer did not work.
I’m really a newbie willing to learn more. Thanks for your patience.
Martin100 (rep: 4) Jul 21, '21 at 7:47 pm
Martin

My mistake- please see revised file and Answer. It was late here and, at the last moment, I declared the new variables (you don't always have to but it's good practice) without re-testing and forgot  to change the new line Set yearSheet = Sheets("Monthly 2021") to remove my " 2021" addition since your navigation buttons didn't work for "Month" with that addition.
John_Ru (rep: 1557) Jul 22, '21 at 1:39 am
Hi John,
Thanks for all your help! It is now posting To the "monthly" sheet but it skipped the general ledger.
Martin100 (rep: 4) Jul 22, '21 at 7:32 am
Martin

Please check using version "...v0_c.xlsm" in the Answer- I just tried it again with a Bad Debt for July 2021, it adds the value to General Ledger and increases the Bad Debt value for July in Monthly. Likewise for Advertising. I'm completing the form in Data Input then clicking the Submit button BTW.

If I try "Other direct costs", again it  isn't found in Monthly and I get an error message (plus nothing happens). For this to work properly, your column B in Monthly must echo column A (Category) in the SOURCE sheet. 

BTW this comment ought to be in the Answer discussion.

Also I just realised you have removed both files from your original question.
John_Ru (rep: 1557) Jul 22, '21 at 8:02 am
No joy?
John_Ru (rep: 1557) Jul 22, '21 at 10:03 am
Joy! Thanks for your help! Much appreciated. -Martin
Martin100 (rep: 4) Jul 22, '21 at 11:05 am
Glad you got it working and thanks for selecting my answer.

Please check the entries in Monthly amdth your catageory validation list  and that rounding up the tax value by a cent is the right thing to do (I think it's better not the understate the amount owed to the authorities!) 
John_Ru (rep: 1557) Jul 22, '21 at 11:16 am
Add to Discussion

Answers

0
Selected Answer

Martin

Here's an approach which should work, assuming you have a workbook for a single year (as you say).

In the revised code and file below (changes in bold), I add a new variable for the summary expenses "yearSheet" and set it to Monthly. A couple of other variables are added for row and column numbers.

Then, BEFORE your code transfers values to the General Ledger sheet, thereis a check that the corrrect year is being used in the form then three steps to add the value into Monthly. In each case a failure will give an error mesage suggesting an action and exit the sub, leaving the form data in place (without transferring the data to the General Ledger sheet).

In the first, it compares the entered month (converted to lowercase) with the the first 3 characters of the full months listed in row 3 of Monthly (agin in lowercase) and finds the matching column number.

In the second step. it loops down column B matching the entered Category (and returns the row it makes a match).  I thought that the error MsgBox probably wasn't needed but it showed that the Category Sub-contracts appears in your form (/sheet SOURCE) but not in Monthly.

That locates the row and column for the value and the third step just adds the value from Data Input to whatever is already there.

On Data Input form, I notice that you're not doing any rounding of gross values (even though your custom cell formatting might make you think the number is just two decimal places) so have unnecessary decimal places will appear in General Ledger and might produce summing errors later. To fix that, I changed the formula in Cell I11 (FIELDSVALUE) to:

=I9-ROUNDUP((I9/(1+E11)),2)
  where the ROUNDUP will round the tax portion up to two decimal places i.e. the nearest cent (for dollars).

Here's the revised code, changes in bold:

Sub Store_Data()
' TeachExcel.com
' Takes data from one worksheet and stores in in the next empty row on another worksheet.

Dim sourceSheet As Worksheet
Dim dataSheet As Worksheet
Dim yearSheet As Worksheet, Mnth As Integer, Cat As Integer, n As Long 'variables for year's expenses etc
Dim nextRow As Integer

' Make some sheet variables so we can use those instead of hard-coding sheet references in the code.
Set sourceSheet = Sheets("Data Input")
Set dataSheet = Sheets("General Ledger")
Set yearSheet = Sheets("Monthly")
' Get the next empty row from the Data sheet.
nextRow = dataSheet.Range("A" & dataSheet.Rows.Count).End(xlUp).Offset(1).Row

' ### Copy gross value to the year sheet ###
'First check correct year
If sourceSheet.Range("F5").Value <> 2021 Then
    MsgBox "Stated year is not covered by this workbook, please check then re-enter form"
    Exit Sub
End If

'1. Find month column
For n = 6 To 50
    If LCase(Left(yearSheet.Cells(3, n), 3)) = LCase(sourceSheet.Range("D5").Value) Then Mnth = n 'use lower case of Month (c.f. full)
Next n
If Mnth = 0 Then
    MsgBox "Month not found on Monthly sheet, please check then re-enter form"
End If

'2. Find category row
For n = 3 To 60
    If yearSheet.Cells(n, 2).Value = sourceSheet.Range("I15").Value Then Cat = n 'Income/Expense Category
Next n
If Cat = 0 Then
    MsgBox "Category not found on Monthly sheet, please check, correct and re-enter form"
    Exit Sub
End If

' 3. add gross value to what's there
yearSheet.Cells(Cat, Mnth) = yearSheet.Cells(Cat, Mnth) + sourceSheet.Range("I13").Value 'Nett value
MsgBox sourceSheet.Range("I15").Value & " value transferred to sheet: " & yearSheet.Name ' this could replace Success msg in Validate sub

' Input the form values into the Data sheet.
dataSheet.Cells(nextRow, 1).Value = sourceSheet.Range("D5").Value
dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("E5").Value
dataSheet.Cells(nextRow, 3).Value = sourceSheet.Range("F5").Value
dataSheet.Cells(nextRow, 4).Value = sourceSheet.Range("I7").Value
dataSheet.Cells(nextRow, 5).Value = sourceSheet.Range("I9").Value
dataSheet.Cells(nextRow, 6).Value = sourceSheet.Range("I11").Value
dataSheet.Cells(nextRow, 7).Value = sourceSheet.Range("I13").Value
dataSheet.Cells(nextRow, 8).Value = sourceSheet.Range("I15").Value
dataSheet.Cells(nextRow, 9).Value = sourceSheet.Range("I17").Value

'Clear Data
sourceSheet.Range("I7").Value = ""
sourceSheet.Range("I9").Value = ""
sourceSheet.Range("I15").Value = ""
sourceSheet.Range("I17").Value = ""

End Sub
You can improve it by finding last row and column for the n= find loops (instead of the nominal upper limits I set) if you like.

Hope this helps.

Discuss

Discussion

Martin

The answer and revised file should resolve the issues you raised. You might want to disable the line MsgBox "Success!" in Sub Validate_Form() since it can appear after an error was detected by my new code.

If this works, please remove the off-putting words at start to your question i.e. "Unfortunately the file attached to the answer did not work".

Next time, please record feedback/errors in the Discussion to the answer (but put question clarifications in the original question).
John_Ru (rep: 1557) Jul 22, '21 at 3:31 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login