Hi!

I have to copy paste an extensive amount of data from multiple workbooks and worksheets to a summary workbook. What I am looking for is a way to double check if the data that I past in the summary is from the right workbook, or display the file name.

Example

Workbook1.xlsx contain 2 worksheets each containing 4 different sets of data
Workbook2.xlsx contain 1 worksheet with 4 sets of data

Now I have to form the summary.xlsx by picking the first set of workbook1.xlsx worksheet1, first set of workbook2.xlsx, first set of workbook1 worksheet2, then move to the second set of data, etc...

I know some of you would code the process in a macro or something, but with the different files and switching from on the the other using different worksheet, I don't trust the automatic process.

I found out the past link method the other day and I believe it is part of the solution as it actually display the source of the data in the fx area of the cell. The missing element for me would be to have in the summary workbook a column that display the file name display in that fx area so I don't have to look up every cell to double check if I actually paste the right thing at the right place.



I tried the =cell function but it display the name of the workbook I am calling the function from or in the example case summary.xlsx

I want something that will display in the summary.xlsx for the data that I paste workbook1.xlsx or workbook2.xls, if I can also get workbook1.xlsx worksheet1 that is even better.

Thanks for the reply !

In each source workbook (Workbook1, Workbook2), why not add this formula in a cell right above your sets of data, to identify the workbook for each set. Example:

In Cell A1 of Workbook1:
=cell("filename")

Then, in your summary file, link to Cell A1 in Workbook1, in order to pull that workbook's filename.

Do this for each set of data in your Summary.xls

This may not be the solution for you, though, as I did not understand this: "The missing element for me would be to have in the summary workbook a column that display the file name display in that fx area so I don't have to look up every cell to double check if I actually paste the right thing at the right place."

If what I suggested DOES work for you, then the caution is, using a link to a cell like this can be vulnerable to typos. You might copy data from Workbook2 into your summary.xls, but then accidentally grab a link to a different set of data in Workbook1.

Good Idea, I will keep that in mind for the future, now I found my solution with =formulatext

It work great and directly on excel 2013 and I had to create the function in 2010. Should work for previous too according to my reference

https://www.youtube.com/watch?v=wpRGTvhp1cY

Thanks anyway!