Copying selected data from different workbooks with 1 template

[Kagiri]

Hello,

So sorry to trouble everyone but i have some questions on excel

Background
I want to copy data, both numbers and text from selected cells from 3 to 4 different workbooks (Excel file A, B, C) into one summary report (Excel File Summary)

so my formula is =[BookA.xlsx]Sheet1!$B$4
which work pretty well, i can send out the excel without losing the data.

Question
My question is i have a few more summary reports to do, and the data are from the exact same cells (over 50 different cells) but the file name is different.
Is there a formula i could use to change all the file name and maintain the cells?

i.e. right now i am using

=[BookA.xlsx]Sheet1!$B$4
=[BookA.xlsx]Sheet1!$B$5
=[BookA.xlsx]Sheet1!$B$6

is there a way to automate the formula so that the Workbook name changes with only 1 edit?
I tried changing the formula to =[A1Sheet1]Sheet1!$B$4 where A1 is =[BookA.xlsx]
but when i tried to edit =[BookA.xlsx] to =[BookB.xlsx] i got an error message.

Does anyone have any experience with this?

Thanks a bunch in advance and i really appreciate any help at all =)

Cheers
Kagiri

[Azam Ali]

Use indirect function

write BookA somewhere. e.g. in cell # J1 and use

=INDIRECT("["&$J$1&".xlsx]Sheet1!$B$4")

workbookname is use as input in this indirect fucntion. similarly you could also use sheet name, column & row as an input in indirect formula

[JBeaucaire]

The INDIRECT() option will only work if all of your referenced workbooks are open. If they will be not be open you will need to have correctly formatted standard formulas.

This suggestion isn't instant, but it is easy...

1) Setup your first table of references to collect all the needed data from the first workbook.

2) Copy the entire first section and paste into position for your second section from the second workbook

3) Highlight all the cells for the second section

4) Press CTRL-H to open the REPLACE wizard, open the OPTIONS and make sure it is searching in formulas and NOT whole cell matches... replace BookA with BookB.

5) Repeat steps above to create updated sections for your other workbooks.

[Kagiri]

Thank you so much

Azam Ali the indirect function works great and JBeaucaire thank you so much for explaining the shortfall of the indirect function and I never thought of using the Crtl H function

Thank you both so much

Cheers
Kagiri

[Kagiri]

Dear All, I just hit another brick wall, for the Indirect function, if the File name has a space, it wont work

I.e. If the file name in Cell A1 is ABCreport it works fine with Indirect
If the file name in Cell A1 is A B C report, Indirect doesnt work

So sorry for the trouble, but does anyone know a way around this apart from changing all my hundreds of filenames?

Cheers
Kagiri

[Azam Ali]

it is not necessary to have no space in file name for indierct function. there might be some other reasons

[JBeaucaire]

When there is a space, you'll need to expand the formula like so (look carefully for the two characters in red I added:

=INDIRECT("'[" & $J$1 & ".xlsx]Sheet1'!$B$4")

[Kagiri]

Oh my god! JBeaucaire you are a life saver!!!
I've been looking up on the web all day looking for an answer and just as i was about to give up i saw your post =)

Thank you thank you thank you

Cheers
Kagiri

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[Kagiri]

JBeaucaire, i was just about to set the post to solve, but it seems there is another issue, it is unrelated to the formula, but when i saved my file with the indirect formula, and sent it via email to my boss and customer, all the cells displayed #ref.

is there a way for me to save the cell data?

Thank you in advance

Cheers
Kagiri

[JBeaucaire]

Yes, print the file to PDF and send them the PDF. It's actually a pretty bad habit to send raw data files to others.

[Kagiri]

I thought of saving the file as a PDF too, but the biggest issue i face is when i need to perform adjustments to the copy and i have to open 50+ different excel files to synce the data back in

>.<

Cheers
Kagiri

[JBeaucaire]

You can't perform adjustments to a PDF copy of a file, the adjustments would be in the original files, that's the point of not passing out Excel documents to others. No way I'd accept the possible chaos of other people changing data that is my responsibility. But there you go....

[Kagiri]

Dear All,

I know this post is labeled solved but i figure how to save the linked data from one workbook to another. After I finished all my linking, i copied everything and pasted it and clicked on the paste option/paste special button and change the option to value only and poof you lose the formula and you only get the value!!~

Cheers
Kagiri

[Kagiri]

Dear All,

I know this post is labeled solved but i figure how to save the linked data from one workbook to another. After I finished all my linking, i copied everything and pasted it and clicked on the paste option/paste special button and change the option to value only and poof you lose the formula and you only get the value!!~

Cheers
Kagiri