How to simplify data entry

[Closed]
Report
Posts
8
Registration date
Monday May 1, 2017
Status
Member
Last seen
May 3, 2017
-
 x -
Hi All

ok I have a daily sheet that i fill in every day it contains 5 fields

I have another worksheet that i then copy the information from the the 5 fields and collate that information weekly monthly so i can see how i am tracking through out the year

is there a easier way to automate this so Just enter the 5 fields

date
myself of Wife
daily Purchases
daily card sales
daily total takings


then on another worksheet automatically update by adding the cell in the correct position

really appreciate any help i can get as i have 6 months of data that my wife has asked me to input

5 replies

Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello Gofreo,

Hmmm. Wouldn't a nick-name like GoCollingwood be better? ;-)

Six months of back entries to catch up on! Geez mate, I'd be telling the missus to do it herself! However, if she sees this, she'll probably kill us both! So, let's try and sort this out for you.

By fields, do you actually mean columns? Such as:-

Column A: Date
Column B: Myself or wife
Column C: Daily Purchases
Column D: Daily Card Sales
Column E: Daily Total Takings

with all the relevant data in the rows below the headings?

You then would like the data automatically transferred to a second sheet (set out the same as the first) for your further analysis/tallying?

Let me know if I have understood your query correctly.

Actually, it would be good if you could upload a sample of your work book to a free file sharing site such as DropBox, ge.tt or Sendspace then post the link to your file back here. This would make understanding/solving your query much easier. Use dummy data in the sample.

Cheerio,
vcoolio.
Posts
8
Registration date
Monday May 1, 2017
Status
Member
Last seen
May 3, 2017

LOL at least you had a win on the weekend but that is another story


I am even trying to learn VB to get it done but not having much success see below



Private Sub cmdSend_Click()
Range("a2").Select
ActiveCell.End(xlDown).Select
Lastcolumn = ActiveCell.Column
'MsgBox Lastcolumn
Cells(Lastcolumn + 1, 1).Value = txtDate.Text
Cells(Lastcolumn + 1, 2).Value = txtName.Text
Cells(Lastcolumn + 1, 3).Value = txtEvent.Text
Cells(Lastcolumn + 1, 4).Value = txtNotes.Text
Cells(Lastcolumn + 1, 5).Value = txtCoins.Text
Cells(Lastcolumn + 1, 6).Value = txtCard.Text
Cells(Lastcolumn + 1, 7).Value = txtFuel.Text
Cells(Lastcolumn + 1, 8).Value = txtMilk.Text
Cells(Lastcolumn + 1, 9).Value = txtOther.Text
Range("a5").Select
txtDate.Text = ""
txtName.Text = ""
txtEvent.Text = ""
txtNotes.Text = ""
txtCoins.Text = ""
txtCard.Text = ""
txtFuel.Text = ""
txtMilk.Text = ""
txtOther.Text = ""
End Sub

not sure what i am doing wrong but then again i have little to no experience trying to work my way through Youtube

I run a small mobile coffee business and the wife has been onto me to catchup but you know how it is lol

anyway any help would be great (the above has taken 8 hours)
Posts
8
Registration date
Monday May 1, 2017
Status
Member
Last seen
May 3, 2017

Posts
8
Registration date
Monday May 1, 2017
Status
Member
Last seen
May 3, 2017

Posts
8
Registration date
Monday May 1, 2017
Status
Member
Last seen
May 3, 2017

I hope that ive done it ok
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello GoFreo,

Try the following code:-
Private Sub cmdSend_Click()

nr = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row + 1

Range("A" & nr).Value = txtDate.Text
Range("B" & nr).Value = txtName.Text
Range("C" & nr).Value = txtEvent.Text
Range("D" & nr).Value = txtNotes.Text
Range("E" & nr).Value = txtCoins.Text
Range("F" & nr).Value = txtCard.Text
Range("G" & nr).Value = txtFuel.Text
Range("H" & nr).Value = txtMilk.Text
Range("I" & nr).Value = txtOther.Text

Unload Me

frmdata.Hide

End Sub


It should add the data from the form to sheet1 row by row.

In a standard module, place the following code:-

Sub Clear()
Unload Me
End Sub


This will clear the form.
Line 17 of the code will hide the form.

Add the appropriate headings to sheet1 and in another standard module, place the following code:-

Sub Showfrm()
frmdata.Show
End Sub


then assign it to a button on sheet1. When you click on the button, the form will show up on sheet1.

I hope that this helps.

Cheerio,
vcoolio.
Posts
8
Registration date
Monday May 1, 2017
Status
Member
Last seen
May 3, 2017

Hi Mate thanks for your help its coming along just to show you were i am at

Private Sub cmdFrom_Click()

End Sub

Private Sub cmdSend_Click()

nr = Sheet1Data.Cells(Rows.Count, 1).End(xlUp).Row + 1

Range("A" & nr).Value = txtDate.Text
Range("B" & nr).Value = txtName.Text
Range("C" & nr).Value = txtVehicle.Text
Range("D" & nr).Value = txtEvent.Text
Range("E" & nr).Value = txtNotes.Text
Range("F" & nr).Value = txtCoins.Text
Range("G" & nr).Value = txtCard.Text
Range("H" & nr).Value = txtFuel.Text
Range("I" & nr).Value = txtMilk.Text
Range("J" & nr).Value = txtOther.Text

Unload Me

frmdata.Hide

End Sub

Private Sub Label1_Click()

End Sub


Private Sub cmdSortByDate_Click()
'
' SortByDate Macro
'

'
Cells.Select
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("A2:A10"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("A1:J10")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub



Private Sub cmdSortByName_Click()
'
' SortByName Macro
'

'
Cells.Select
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("B2:B12"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("A1:J12")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub

Private Sub cmdSortByVehicle_Click()
'
' SortByVehicle Macro
' Sort By Vehicle
'

'
Cells.Select
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("C2:C11"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("A1:J11")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select

End Sub

Private Sub ctlDate_Click()

End Sub

Private Sub UserForm_Click()

End Sub

the clear for isn't working

and i tried to add a calendar but that didn't work out

and the other thing is the cheese and kisses, still not happy with where i am at. lol Some things wont change ;)
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello GoFreo,

Good to see that you are having a go.

But I don't quite follow what you're up to! Perhaps Nat Fyfe's possible departure has you all knotted up inside!

Do you feel like giving us a complete run-down on what you're trying to do?

Cheerio,
vcoolio.
Posts
8
Registration date
Monday May 1, 2017
Status
Member
Last seen
May 3, 2017

Hey Mate lol

Funny,
I heard that all Freo's senior players were off to St Kilda, all but Nat was quitting to take a musical career.

and the other news was that Trump was immigrating to Australia due to a lack of pollies over here ;)



Man FAKE news is every were


well back to the spread sheet

the first part is done and works a treat but when you input the data i thought i could use a pop up calendar to simplify the input

Ive saved it in drop box as well if you wanted to have a look

Scott
aka GoFreo
Posts
8
Registration date
Monday May 1, 2017
Status
Member
Last seen
May 3, 2017

the main object is to be able to used the data to compare month by month and what activity is best

so Step
1. was to input all the data and then

2. create a work sheet that then shows and compares the data

so we use a small time sheet to recorded the info and that's what has been building up over the last 6 months or so

Thanks for all your help Mate
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
G'day Scott,

Yes, the fake news has taken a foot-hold everywhere. I suppose papers need to be sold. I take it all with a pinch of salt.
All the Freo senior players could form a band at St. Kilda perhaps!

Have you considered scrapping the form method of entering data and just directly entering the data to the spreadsheet? Either way, the data has to be entered. I personally don't have much time for forms.

For my own business, I have a large-ish workbook with tabs for each month and a totals tab (plus a Summary sheet just for my Accountant). From the totals tab, at a glance I can analyse/compare each month's performance and then either be happy or go off and cry. The individual month tabs hold all my income and expenses records which are then automatically summarised in the Totals sheet. The income section is automatically populated by macro from an Invoice Register in another workbook. My Accountant loves it (but still charges like a wounded bull!).

Would you be willing to have a look at something similar?

Cheerio,
vcoolio.

P.S. I get the missus to do all the entries for me each month ;-)
It seems you could take advantage of the built-in form here.

https://www.timeatlas.com/excel-data-form/


Its easier than learning VBA.