I do lot of calculations, copying from one excel to another excel. No matter how best you do, it ends up in minor mistakes in copy pasting.
Is there any mechanism where we can cross link with one sheet and compare whether values are correctly pasted in another sheet.
Please give your inputs.
The best way is to always work with equations and not with value-based copy/paste.
When I work with Excel, I usually organize the work in the following way:
1. I create a sheet or a file of data with 'DB'/'data'/'database' or similar in the name;
2. then, I do calculations in different sheets/files grouping them according to a scheme meaningful to the problem (It might be step-wise in an algorithmic problem, according to categories, or else...). The use of sheets or files clearly depends on the complexity of your task; the amount of data and calculations; if, what and how you have to share and present the results.
Suppose in you're doing calculations in the generic sheet 'calculations-part1' of the file 'my-first-job' and you want to input data existing somewhere in a generic cell, let's say K45. 3 cases are possible.
1. Data is on the same sheet and same file, i.e. sheet 'calculations-part1' of the file 'my-first-job' . You just type "=dataROWdataCOLUMN". Let's say data is in A13, you type in cell K45: "=A13".
2. Data is on a different sheet in the same file, i.e. sheet 'calculations-part2' of the file 'my-first-job'. You type "='sheet-name'!dataROWdataCOLUMN". Let's say data is in B17 on sheet 'calculations-part2', you type in K45 on sheet 'calculations-part1': "='calculations-part2'!B17".
3. Data is in a different file, i.e. sheet 'other-calculations' of the file 'my-second-job.xlsx'. You type "='full\path\to\folder\[excel-file-name.xlsx]sheet-name'!dataROWdataCOLUMN". Let's say data is in F3 on sheet 'other-calculations' of file 'my-second-job.xlsx', located in 'C:\My-Docs\My-Excel-Works\Todays-Work'. You type in K45 on sheet 'calculations-part1' of file 'my-first-job': "='C:\My-Docs\My-Excel-Works\Todays-Work\[my-second-job.xlsx]other-calculations'!F3". You can use also relative paths if the input Excel file is in a subtree of the folder where the file you're working with is located. I generally prefer working with absolute paths as the behaviour is more stable.
In this way, when you change a value (data or intermediate calculation) in another cell, sheet or file, all dependent calculations will be updated accordingly without any user intervention. Even graphs will be updated automatically.
Furthermore, from the point of view of revision, you just need to check values in one place and they will be corrected automatically everywhere. You just have keep sheet names, file names and folder names consistent or, otherwise, update them.
This final step could be automated, but you should program with VBA to achieve it (actually, it won't be a very complex program, but you'd still need to code it).
I hope this answer helps you.
Best,
Luca
Hi Luca,
Thanks a lot for the valuable information. It was very useful.
In my situation, the above said steps are involved. In the step, i am expected to provide only the final values based on the calculations i made earlier.
So, i had to paste special only the values in the respective cells. At times, i end up pasting values of US to UK or 2014 data into 2015. So, is there any way i can avoid this error. Any VBA to achieve this feat. Your inputs will be useful.
Are you using Excel only? or are values coming from external programs, too?
- Badges
- Science topic
- Similar topics
- Applied Mathematics
- Calculations