Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
I have about 35 excel files, all formatted the same way but with different data. Is there any mergy-type way of getting all the data into one file that is better than opening them all and cutting pasting? Please tell me there is. God bless you all.
If you have to ask this question then the quickest way of doing it is the way you've described. Sorry.
You could write VBA to do it but thats prob more of a pain than opening them all and doing a bit of a ctrl-c/v frenzy
OK. Damn. I'll have to pay one of my kids to do some C&P
All the data on the same sheet?
Could you not export them all as CSV, concatenate all of the CSV's together and then re-apply the formatting?
If you want it all on separate sheets ignore me.
There is, yes. I did exactly this the other day. You have to have all the files open.
Use this:
=INDIRECT("'C:\Expenses\file.xlsx]Expenses'!AL37")
Where AL37 is the cell I want and Expenses is the sheet name.
File.xlsx has to be open though.
I had a load of files that were named as dates like this 2010-10-10.xlsx so I made a column with the dates in it and then used this to generate the file name where C was the column with the dates in it
=INDIRECT("'C:\Expenses\[" & TEXT(YEAR(C2), "00") & "-" & TEXT(MONTH(C2), "00") & "-" & TEXT(DAY(C2), "00") & ".xlsx]Expenses'!AL37")
You can download a function from the 'net that do the same as INDIRECT but will open the file if it's not open. It's called PULL, [url= https://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/e249f6c074a3adfd?hl=en ]this link may work but you may wish to google further.[/url]