Solved

Excel - look for new cell data then copy paste information beside cell

Posted on 2016-09-12
21
42 Views
Last Modified: 2016-09-16
I have a great working workbook.
I need to add another function to it.
When I add a name to the Master Log sheet at the bottom, right now it automatically adds that same name to the ExpDate sheet at the bottom (5 times), this works great. I want the next two groups of cells to copy down from the group above.
helper image
Right now the drop down in Update sheet looks at data from MAster Log and displays the the Name and entire row data into tables in the Update sheet. This works great. This is also how we update the info and send back to Master log by hitting the Update Data button.

The drop down only shows the current names in master log, I would like an input box to ADD a new name to the master log from the Update sheet as well. Any changes to the tables currently are sent to master log when hitting the update data button. So a new name should be sent as well with any additions for that name in the tables..... Somehow we need to clear the drop down name so the program doesn't get confused which data to send on button click.
 04.JPG
Let me know if this can be done on one question or if we need to split it up.
Thanks so much
Chris
Stripped-expiry-email-Sep12.xlsm
0
Comment
Question by:chris pike
  • 11
  • 10
21 Comments
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
Hi Chris

Do you want to use an InputBox or  a UserForm? Another Option would be to simply use the current layout but use the New Name cell

I'll take a look at this for you
1
 

Author Comment

by:chris pike
Comment Utility
Thanks Roy,
Doesn't really matter which way, as long as we can:
1) Call up the name from the master log, populate the tables with its data
2) And enter a new name on the same sheet that would push to master log with what ever data is inputted for that person onto the tables.. This being said, the previous work that we did, the copy of the name and paste 5 times still needs to work when the new name gets pushed to the master log.
Thanks\
Chris
0
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
It seems best to use a cell in the input sheet.

Try this and let me know if it is what you need. I'll check back after work.
Stripped-expiry-email-Sep12.xlsm
0
 

Author Comment

by:chris pike
Comment Utility
Thanks Roy, It sends the name to the master just fine. However it populates 10 names into the ExpDate instead of 5.

Was really hoping that we could somehow clear the tables in "Update" sheet, and be able to enter the data as well as new name into the tables for the new person at the same time. If we send a new name to master we will still have to manually go to master to enter his info as well. So the info in the tables will also send to master when a new name is entered. How does that sound??

Thanks so much./
Chris
0
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
It was only adding 5 names for me.

Where would the other information come from?

Edit: just gone through the code and you have worksheet change event that adds five rows as well. I've cancelled that code and my works fine.

Let me know what else you need to add. It may be that you need a userform
Stripped-expiry-email-Sep12--v1.01-.xlsm
0
 

Author Comment

by:chris pike
Comment Utility
OK great I figured out your logic, Works  great. I figured out the double set of five, good catch.

I was driving to work this morning and I thought of a much easier solution for inputting data into the tables for a new name. This is so the user doesn't have to go back and forth between sheets. He won't have to enter name in one place and then enter the data for the new person in a different place.

When a new name is entered, it will be available in the drop down right away, correct? (yes is should)
What if when a new name is entered, we tell the drop down to select the last name in the list then the tables will automatically be ready for change OR new info to be updated? And that is it? When a name is selected in the drop down it already preps the tables, so this should work great.,

Does that sound doable?
Thanks ROY
You are a RockStar
Chris
0
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
I'll have a look at that. I'm off to babysit my grandson so I'll look as soon as I can
1
 
LVL 17

Accepted Solution

by:
Roy_Cox earned 500 total points
Comment Utility
0
 

Author Comment

by:chris pike
Comment Utility
Hi Roy,
The adjustment work great, however when I run a report from the dashboard there is a conflict.
This was working before our last edit. When you hit Run Report on the dashboard sheet...
05.JPGIt is in the "mExport" module  :(

Thanks Roy.
Have  a great day. On the West Coast (Vancouver Canada) it is 6:30AM gunna be a long day. It is almost Friday though..
Talk soon.
Chris
0
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
I didn't change anything that should produce that error but I'll check for you.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:chris pike
Comment Utility
Hiiiiiiii Roy..........
Thanks so much
Chris
0
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
The error is not caused by my code.

I changed this

Format(Now, "yyyy/mm/dd")

Open in new window


to

Format(Date, "yyyy/mm/dd")

Open in new window


It runs with no errors for me. I'll see if I can speed it up for you, I did a very similar project last year.
0
 

Author Comment

by:chris pike
Comment Utility
:(  
I never touched any of the code, I used your last edited version.  :(
07.JPG
0
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
In the VB Editor go to Tools in the menu bar. Then select References and see if any are missing, it they are then uncheck them.

Screenshot-2016-09-15-18.33.39.png
0
 

Author Closing Comment

by:chris pike
Comment Utility
Awesome work.
0
 

Author Comment

by:chris pike
Comment Utility
Will me un-ticking the Microsoft Office 16.0 Library give me trouble later?
0
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
Sorry, I didn't mean you to untick MS Office 16. Just any references marked as missing.

Did un-ticking MS Office 16 fix it though?
1
 

Author Comment

by:chris pike
Comment Utility
Yes i unticked the missing one, and it seems to be working now.
That won't cause problems on other files will it?
Thanks so much
Chris
0
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
No it shouldn't cause any problems.
1
 

Author Comment

by:chris pike
Comment Utility
Thanks Roy
0
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
Glad to help.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now