Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2016-09-12
21
Medium Priority
?
57 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 22

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 22

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
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.

 

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 22

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 22

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 22

Accepted Solution

by:
Roy Cox earned 2000 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 22

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 22

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 22

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 22

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 22

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 22

Expert Comment

by:Roy Cox
ID: 41801857
Glad to help.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

609 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