I'm just pulling the contents of cell B3 on Sheet1 into a cell on Sheet2. I had almost 100 ='Sheet1'!?? to type up... The data on Sheet1 isn't all in a column or row for me to view easily so I was pulling it into Sheet2 so it would be in a list.
Since I had so many to type up I figured I would type ='Sheet1'! in the first column then the second column I would type the row then the third column the cell number. The fourth column I put the =A1&A2&A3 so I would get the function of ='Sheet1'!?? but excel doesn't recognize ='Sheet1'!?? as a function.
I ended up getting that to work by copy/pasting the unrecognized functions then doing a find/replace of ' with nothing. Then it pulled in the data from Sheet1 that I was using as my titles for each column.
Yea I have Access, just never really used it much...
*****************This is my scenario and what I'm trying to do*****************
I have 150 workbooks, all containing the same worksheet but with different data, and each workbook is named different. I wanted to pull all of this data into a master workbook so I can view/compare the data.
My plan was to pull in the names of the workbooks then I would be able to reference that cell to create an =WORKBOOKworksheetCELL function to pull the data in for each workbook.
So far (in Master.xls) I have used this to pull in the names of all the workbooks within a folder called Workbooks
Defined a name with this
then put this in column A
That begins the file names in A3 and goes down to A200
Now in B3 if type ='[workbook1.xls]Sheet1'!$C$6 it returns the contents of cell C6 on Sheet1 within Workbook1.xls. Is there a way to replace the file name with a reference to cell A3 so I can just copy/paste and get the data out of cell C6 in all of the workbooks?
But with 150+ workbooks, I'd seriously start looking at some database application. Access, done properly, can do it. Even if only for the data collation aspect. Link the spreadsheets into Access, then link to the sheet in Master.xls.
Painful to get set up, but far, far easier in the long run to use.
Thanks for the reference box changing, that works great.
Do you know of a way to reference a cell that would contain a file name rather than the file name itself in this function: ='[workbook1.xls]Sheet1'!$C$6
I've played with Access before but very briefly... So I would be able to "import" the data from the workbooks into Access then "export" into a master workbook, would the data be updated by simply adding/deleting the workbooks in the folder or would I have to build a form within Access?
If you've only "played with Access before but very briefly", I'd probably not suggest that route. It's pretty powerful, and you can quickly get in over your head.
But with 150+ Excel workbooks, that may already be happening.
I'd have to play with concatenating filenames & cell or range references.