• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

Creating a log-file for repetitive data entry

EE Professionals,

I have a Workbook that has a Input Tab with two columns of data in two separate 15 cell rows.  Each set represents a set of answers that a specific person is responsible for.  What I'm looking to do is each time a name is entered, the rows are cleared and when data is entered, it's "logged" to another worksheet.  When the name changes to another person, it again clears the data and new data is entered.  On the log sheet, the new data is then recorded in adjacent cells so as to show the new data from the new person.  The goal is to collect data from multiple persons without having to copy and paste.  I have attached a sample workbook.

Much thanks in advance,

B.
Log-File-Macro.xlsm
0
Bright01
Asked:
Bright01
  • 8
  • 7
  • 6
2 Solutions
 
Davy2270Commented:
I have put something together for you. Check out the attach.

I have made a button which you have to click for logging your data, as triggering this automatically is a tricky thing to do.
The data in your sets however, will be deleted automatically when an new name is entered.

Regards,
Davy

Log-File-Macro-1-.xlsm
0
 
Davy2270Commented:
One thing I should also mention.
I built in a check that counts the number of entries in a dataset.
If not all item entries have been made, the log action will not be executed for that dataset.
The code will give the user feedback of what has been logged.

with this said, I saw I made a little mistake with the feedback. See attach for adjusted version.

Kind regards,
Davy
Log-File-Macro-1-.xlsm
0
 
Bright01Author Commented:
Davy2270,

Thank you for the quick response..... I was on a plane yesterday so this morning was the first opportunity I had to check out your changes.  This is very good!  However, several small points.  If you clear the Log_File Data (and I can write a clearing routine), and then you put a new name in the input, the model performs correctly.....except, it should only log an entry when a person is inputted into the name field. So -- EXAMPLE:  Smith is in the first input column (F4) and no name is in the second (F22). The data get's logged.  ONLY the first data set is updated (not the second).  Next we put in Jones where Smith was.  The Data set gets appropriately reset (in Input).  Data is entered.  In F22, Thomas is entered and the 2nd data set is filled in.  Now when the file ls logged, the first dataset has two logs (Smith and Jones) while the 2nd data set has one entry which is Thomas.

Make sense?

Much thanks....we're almost there!

B.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Davy2270Commented:
I'm afraid I don't understand what needs to be changed. Can you give printscreens or a result workbook for me to see what you're aiming for?
I'm sure we're almost there.

Regards,
Davy
0
 
Bright01Author Commented:
Yes.

First; The log file should be blank (cleared) before anything is added.  Again, I can write that part.  I had originally put data there to show where/how it goes.

Here are two screen shots.

1.) Only data entered should be logged
2.) It should be able to accomodate blanks in the input (i.e. if you don't have all 15 numbers, it's ok.)

Thank you,

B.


Test-results-for-Logging-Macro.pptx
0
 
ScriptAddictCommented:
I think you'll find that this worksheet does what you want.  



Log-File-Macro-1-v2.xlsm
0
 
ScriptAddictCommented:
Ahh looks like I misunderstood.  
0
 
ScriptAddictCommented:
I made minor changes most points should go to Davy
Log-File-Macro-1-v3.xlsm
0
 
Bright01Author Commented:
ScriptAddict,

Thanks for the input.  I just tested it and it's still  not working correctly.  If I put data into the first set only (the first 15 answers), it should only post that data into the log_file (nothing for Set 2 in the Log File).  If I then put a new name in for the first set in the Input Tab, it should clear the first set and then when I put in new data, it should only log that new data (as another two columns) to the log file (now I should have 2 sets of data, but only for the first set from the Input Tab).  Now if I then put in data for the 2nd set in the Input tab, and hit the log button, only the new data from set 2 on the input tab should be new to the log file.  If I now look at the log file, I have 2 input sets for the 1st set from the input tab, in the log file and 1 input set, in the log tab from the 2nd set in the input file.  AGAIN, only changed data should be mapped into a log file when updated.....not the whole file from the input screen.

Sorry for the complicated explanation and I hope that makes sense.  

B.
0
 
ScriptAddictCommented:
Ah crap looks like that broke when I made the other minor tweek.

Let me adjust code.
0
 
ScriptAddictCommented:
His range included titles, which I didn't take into account on the 2nd tweek.  This should fix it for you.
Log-File-Macro-1-v4.xlsm
0
 
Davy2270Commented:
Easiest way to avoid the set being copied twice is to clear the name from the list you don't want to be logged again. I think that's the issue. Please confirm.

As I come to think of it, you can do this by clearing the ranges Answers1Data en Answers2Data.
Add this to the bottom of your code, before End Sub ;-)

Application.EnableEvents = False
Range("Name1").ClearContents
Range("Name2").ClearContents
Application.EnableEvents = True

Regards,
Davy
0
 
Davy2270Commented:
Erm, please ignore the Application.EnableEvents syntaxes in my former post.
0
 
Bright01Author Commented:
Davy2270,

Not that easy.  Let me provide some better business context.  Someone who enters the data can enter it in both sets or simply one set.  I'm trying to only post to the log file, when a name change occurrs at the top of any set.  I think the issue here is in my original note, I was looking for the macro to recognize a name change in the appropriate cell, and then after someone had stop inputting to the cell set, it would automatically write to the log.  With the button, it creates an event that takes a snapshot rather then looks at the set and recognizes, "new user" post the changes to the log for that particular set, after the user moves away from the set on the input screen.

I hope that makes sense as to what I'm trying to accomplish.  In the end, multiple users can use the same data input screen without having to copy and paste any input if the logging works correctly.

Please don't give up now. ;-}

B.
0
 
ScriptAddictCommented:
So if someone enters a name that has been entered before do you want it to go back and update the old data set?  Or post a new one?
0
 
Bright01Author Commented:
Great question.  If it's exactly the same name, then it would be great if it updated the original responses.  In 98% of the cases, a person will only enter data once in a data set.  However, they may enter data in more then one data set.  So, in the case we have, Smith could enter data in both sets and it would record both of them in the log.  If Jones then enters data into only one set, it adds that data to the correct set in the log file under Jones.  In 98% of the cases, no one will ever go back....say Smith and enter new data unless they have for some reason changed their mind.  Hope that answered your question.

B.
0
 
Davy2270Commented:
@ ScriptAddict: that's what I now am understanding as well.
Bright01, can you confirm?
0
 
Davy2270Commented:
So, one more question. We have two data sets in the input screen, which can have different names. Is that right?
So if you want both datasets filled in for Smith, then you need to enter the name twice.

On the other hand I understand this as well:
If only one data set for a person gets logged, the space for the second data set should be reserved on the Log sheet so that later on data for the other set could be added.

If that's true we should work with only one Name in the input sheet.
0
 
Bright01Author Commented:
Here are the answers to your questions:

"Two data sets in the input screen which can have different names":   YES - Or The Same Name
"Filled in twice"?:  YES

"If one data set gets logged, the space for the second data set should be reserved":  NO  Only data that is entered into the Input screen is recorded.

Here's where the confusion is.  I may have 5 people assigned to Data Set 1 and 3 people assigned to Data Set 2.  This means that I'm not going to have a consistent number of responses between Data Sets.  That's OK. So the end result will be 5 sets in Data Set 1 and 3 sets in Data Set 2.


B.
0
 
Davy2270Commented:
Ok.
I think I got your concept.
see the attach. Play around with it and see if it does what you want.

Regards,
Davy
Log-File-Macro.xlsm
0
 
Bright01Author Commented:
Thanks Guys!

Great job.  I'll be writing a follow on to this shortly.  It took a lot to stick with this one but both models will be useful.

Thanks again,

B.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 8
  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now