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

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

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
chris pike
Asked:
chris pike
  • 11
  • 10
1 Solution
 
Roy CoxGroup Finance ManagerCommented:
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
 
chris pikeAuthor Commented:
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
 
Roy CoxGroup Finance ManagerCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
chris pikeAuthor Commented:
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
 
Roy CoxGroup Finance ManagerCommented:
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
 
chris pikeAuthor Commented:
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
 
Roy CoxGroup Finance ManagerCommented:
I'll have a look at that. I'm off to babysit my grandson so I'll look as soon as I can
1
 
Roy CoxGroup Finance ManagerCommented:
0
 
chris pikeAuthor Commented:
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
 
Roy CoxGroup Finance ManagerCommented:
I didn't change anything that should produce that error but I'll check for you.
0
 
chris pikeAuthor Commented:
Hiiiiiiii Roy..........
Thanks so much
Chris
0
 
Roy CoxGroup Finance ManagerCommented:
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
 
chris pikeAuthor Commented:
:(  
I never touched any of the code, I used your last edited version.  :(
07.JPG
0
 
Roy CoxGroup Finance ManagerCommented:
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
 
chris pikeAuthor Commented:
Awesome work.
0
 
chris pikeAuthor Commented:
Will me un-ticking the Microsoft Office 16.0 Library give me trouble later?
0
 
Roy CoxGroup Finance ManagerCommented:
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
 
chris pikeAuthor Commented:
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
 
Roy CoxGroup Finance ManagerCommented:
No it shouldn't cause any problems.
1
 
chris pikeAuthor Commented:
Thanks Roy
0
 
Roy CoxGroup Finance ManagerCommented:
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!

  • 11
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now