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?