Aug 03 2021 01:47 AM
Good afternoon Community,
I have the following issue.
I have a file with contains
Code: Machine ID
Location: Uses List data validation
The location can change multiple times and I need to copy to a new worksheet the Code# and new location each time.
I have updated a sample file for your information.
@Logaraj Sekar
Aug 03 2021 03:08 AM
Could you please send us more detailed information?
The storage location can change several times
... when, from where to where, how many times?
... is the storage location specified?
. and I have to copy the code # and the new location into a new worksheet every time.
... when (for which cell) does a new worksheet have to be copied?
... at which cell does the data appear?
... or will this manual be entered?
* Knowing the Excel version and the version of the operating system would be an advantage, especially for you.
There are some questions arising from your text look at first suggest themselves.
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
Aug 03 2021 06:04 PM
Thank you, @NikolinoDE
Please find below
The storage location can change several times
When I mention storage location; I referred to the location column value.
. and I have to copy the code # and the new location into a new worksheet every time.
I have updated the file to make it clearer but basically.
I get a list with Code and Location Columns.
Code Column is an ID# value.
Location is the name of a place, usually blank. Then people would have access to the file and change the location; sometimes multiple times.
I need to copy any new location value in the "Record Values" tab including the Code Value.
Aug 03 2021 07:09 PM
I fail to see the logic of you copying the Code# and Location to a new worksheet.
This new worksheet is it just a blank worksheet?
or there are other fields in this new worksheet where you paste the Code# and New Location?
if it is just a New Blank Worksheet, then that would be inefficient because you're already getting the information complete, so I see no value in you copying it into a New Blank Worksheet. This is why Mr NikolinoDE is somewhat hesitant to commit to helping because your narrative has quite big holes in it and you're not narrating the whole situation, where it comes from is absolute you get the Code# and Location name (2 columns), but you don't exactly show what this New Worksheet looks like (so we will assume) it is just a New Blank Worksheet (if it isn't so then that proves the ambiguity your narrative is communicating to the problem solvers).
Moreover, the problem solvers does not understand why you have to copy and paste information that you already have, instead of manipulating the data by adding a date and a time of when you received the data so you can populate somewhere else or just create a pivot table iterating the whereabouts of the Machine at a certain point in time.
cheers
Aug 03 2021 07:49 PM
Aug 03 2021 08:01 PM - edited Aug 03 2021 08:09 PM
I am assuming this query is Power Query in excel?
How often does the file get opened? Every Hour? 2 Hours? 4 Hours? You left that seemingly unimportant/irrelevant part of the narrative
Instead of copying, why not saving the query as a csv file, which records when the data was acquired (Date and Time) in a designated folder. When doing a query by folder, all data saved in that folder are combined and can be manipulated by date/time into a pivot table which will list the machine and the its whereabouts location wise will be iterated in the pivot table. Don't forget to rename it though or probably save the query as a template, where it will update the query and at the same time change the filename to "FileName01", 02, 03... and so on and so forth
cheers
Aug 03 2021 08:27 PM
Aug 03 2021 08:38 PM
So the necessary updating of the file is once in the AM and once at the end of business day, Any other access is incremental other things so maybe once in the morning once at midday and once at end of business day and everytime it is opened to update, save a csv copy to a designated folder much like an update log, any other types of access should be a Power query against the File query that is connected directly to the supplier server. your thoughts?
cheers