Resolved >>> Auto-populate cells with data from other cells

My sheet goes from monday to friday on the coulombs. On the rows i have various data sections for each day. I would like the data on the last day (any day after monday) that there is data for a sheet to input that data into the monday slot when the "master date" is changed on the cheese sheet. The idea here being that the script will take the last entered data for a given sheet, and put it into the monday coulomb on the same sheet when the date is changed. This is kind of hard to explain so if you need clarification let me know. Attached is the sheet i'm working with. Thanks in advance for any help.

nate

Hi

Sorry, but don't follow.

Can you step through what you want to happen using your sheet as the example. So if you change cheese!A2 from 24/9/07 to 1/10/07 what would happen next? Data from where would go to where?


rylo

will do.

after you change the date on cheese!A2-

- It will save the sheet (by date) creating a new file named xxxxx-the date.xls
- each sheet will grab the set of data from the Friday coulomb
- Then it will insert that friday data in the monday coulomb
- it will then erase all data for days tuesday through friday
- thus we have a brand new page for the next week - with the previous weeks data inserted (since no changes will occur over the weekend) in the monday coulomb.

Let me know if more clarification is need. something is lost in translation via the internet, but thank you for your reply.

ciao,

nate

Hi

See how this goes.

Please Login or Register  to view this content.

I've commented out a couple of rows that will change the default drive / directory and put the full path into the saveas file name. Gives you some code options in case you want the file to go somewhere else.


rylo

Thank you very much. This will help. I'll get back with any questions after i have a chance to look at the code.

Originally Posted by rylo

Hi

See how this goes.

Please Login or Register  to view this content.

I've commented out a couple of rows that will change the default drive / directory and put the full path into the saveas file name. Gives you some code options in case you want the file to go somewhere else.


rylo

When i walk through the code it will copy and paste the correct cells data into the monday column. But when i change the date in cell Cheese!A2 it does not run. Is there a way to make this bit of code execute when the date is changed on the cheese A2 cell? Thank you for your help, this does exactly what i need.

Nate

Nate

Right click on the Cheese sheet tab, select view code and insert

Please Login or Register  to view this content.

I deliberately left it away from an event. If you make a change to the cell and there is nothing in the Friday data, then it will overfill with nothing. Are you sure you don't want to be able to control the activity - say with a button???

rylo

I hadn't thought about doing it that way, but yes a button would be a good idea. Ill try that out and see how the technicians like it. Thanks again for your help.

nate

Ill write back later today once i have a chance to check out the code.

After looking at the code it seems you are right. It is best to control the action of the copy paste but i hadn't even thought about a situation where fridays data wouldn't be complete. I know i have asked a lot already, but would there be a way to aggregate the last set of data in each row, paste that into say friday or one of the weekend days columns, then have that completed set of data be placed into monday? Just a thought, thank you very much for your input.

nate

Nate

1) define "aggregate the last set of data in each row". Just what do you mean by this. Perhaps give a more detailed example file with 2 or more days data and what you would expect to see.

2) Putting the "aggregated" results from the entire week into a Saturday, then moving that to the start of the week would seem a good way to go. You could also put in some testing to check certain criteria and not proceed if some critical data is missing.

Can you give some thought to point 2, and work out a business process that would ensure that a required minimum data is available before the transfer takes place.

Sometimes it is better to stop and have a good think about different scenarios and work out your approach before trying to code a result.


rylo

Originally Posted by rylo

Nate

1) define "aggregate the last set of data in each row". Just what do you mean by this. Perhaps give a more detailed example file with 2 or more days data and what you would expect to see.

2) Putting the "aggregated" results from the entire week into a Saturday, then moving that to the start of the week would seem a good way to go. You could also put in some testing to check certain criteria and not proceed if some critical data is missing.

Can you give some thought to point 2, and work out a business process that would ensure that a required minimum data is available before the transfer takes place.

Sometimes it is better to stop and have a good think about different scenarios and work out your approach before trying to code a result.


rylo

1) Say you have the IP address field filled out on tuesday...but there is not another entry for this IP address row all week. I would want the script to pull tuesday's value and insert that into the saturday slot as the last current data for IP address. It would perform a similar function for every row, grabbing the last available data for that row, and inserting it into the saturday cell. It would ignore empty cells.....thus in the saturday column you would have a completed column of data representing all the latest info from each row. Then it would take the completed saturday column and paste that to the monday column thus the next week would start out with a full colomn of data for the sheet.

2)The only critical data that must be inserted is if the status is set to 3(red) the sheet will prompt the user (with a msgBox) to insert a comment...but this functionality works as of now...unless you have a better idea on how to do that?

Basically there is no minimum amount of data for the sheet. Certain aspects of the test machines (cheese, grommet, etc...) will change independently of other aspects. It is completely possible to have a machine change a number of the software version numbers without changing any of the firmware version numbers(FW), IP address, etc... That's why i was thinking it would be good to have a script that would aggregate the data across the selection, and artificially create a completed set of data in the saturday column. Thus this way when the tech starts a new week of status reporting, he or she, at least has a complete set of data for monday. If there was to be a piece of data missing from monday (no data in that row for several weeks i guess would be the senerio) we could use some type of msg box or something of that nature to alert them to this fact...that actually might be a good idea, this way the tech could investigate that value and enter it in manually. Thanks again for all your help.

Cheers.
nate

Nate

Some more questions.

1) All of the sheets except Grommet Jnr basically end at row 32, while grommet ends in row 33.

Would it be a problem if the other sheets had an additiional Adhoc Area row so that all the sheets had a consistent range size?

2) The status seems to be already filled in across the entire week range. What exactly do you want to copy from this row, or is it to be left as it stands???


rylo

Originally Posted by rylo

Nate

Some more questions.

1) All of the sheets except Grommet Jnr basically end at row 32, while grommet ends in row 33.

Would it be a problem if the other sheets had an additiional Adhoc Area row so that all the sheets had a consistent range size?

2) The status seems to be already filled in across the entire week range. What exactly do you want to copy from this row, or is it to be left as it stands???


rylo

Adding an additional row would not be a problem.

If the script could be setup to copy the status of the last day there is any data entered that would be fine. If the script could set all the "status" row entries past the one it copies to a status of 2 that would be awesome. Either way the status section will be updated on monday so even if it doesn't copy that data it's not a show stopper.

Nate

Have a look at the attached. I got lazy and just made all the status lines 2.


rylo

thankx rylo thats awesome!! It does exactly what i need it to do. Thanks again.

nate

whats your paypal? Seriously ill send you a donation for your help....thanks again

nate

ROFL, ROFL, ROFL.

Nate

What happened? Did you get a promotion out of it???

Thanks, but don't worry about it.

rylo

haha, na no promo but it did help a lot. Are you sure, i really wouldnt mind making a donation?

Nate

quite sure, but thanks for the thought.


rylo