Solved

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

Posted on 2016-09-12
21
52 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 10
21 Comments
 
LVL 20

Expert Comment

by:Roy_Cox
ID: 41794567
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
ID: 41794730
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 20

Expert Comment

by:Roy_Cox
ID: 41795477
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:chris pike
ID: 41795989
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 20

Expert Comment

by:Roy_Cox
ID: 41796456
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
ID: 41797914
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 20

Expert Comment

by:Roy_Cox
ID: 41798341
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 20

Accepted Solution

by:
Roy_Cox earned 500 total points
ID: 41799210
0
 

Author Comment

by:chris pike
ID: 41799765
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 20

Expert Comment

by:Roy_Cox
ID: 41800203
I didn't change anything that should produce that error but I'll check for you.
0
 

Author Comment

by:chris pike
ID: 41800207
Hiiiiiiii Roy..........
Thanks so much
Chris
0
 
LVL 20

Expert Comment

by:Roy_Cox
ID: 41800218
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
ID: 41800239
:(  
I never touched any of the code, I used your last edited version.  :(
07.JPG
0
 
LVL 20

Expert Comment

by:Roy_Cox
ID: 41800280
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
ID: 41800317
Awesome work.
0
 

Author Comment

by:chris pike
ID: 41800392
Will me un-ticking the Microsoft Office 16.0 Library give me trouble later?
0
 
LVL 20

Expert Comment

by:Roy_Cox
ID: 41800968
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
ID: 41801540
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 20

Expert Comment

by:Roy_Cox
ID: 41801736
No it shouldn't cause any problems.
1
 

Author Comment

by:chris pike
ID: 41801739
Thanks Roy
0
 
LVL 20

Expert Comment

by:Roy_Cox
ID: 41801857
Glad to help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

691 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