Excel Function question

[indyitguy]

Is there a way excel will recognize the value of [=A1&A2&A3] as a function?

Example:

Cell A1 is =
Cell A2 is 'Sheet1'
Cell A3 is !B3
Cell A4 is =A1&A2&A3

That would make Cell A4 produce ='Sheet1'!B3 so shouldn't it actually equal what is in cell B3 on Sheet1?

 

[corroded]

Hmmm.. I've done oddball cell to cell references and linking cells between sheets. Not sure how (or why) you would link a cell to a full sheet...

Sounds like you are attempting database functions on Excell... Got Access?

 

[USAFRet]

You're missing the opening and closing parens.

=(Sheet1!B3)
And I'm not sure an = all by itself would be recognized as part of a formula.

 

[USAFRet]

What version of Excel, and what are you actually trying to accomplish? There's probably another way to reach your end game.

 

[indyitguy]

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
=FILES("C:\Workbooks\*.*")&T(NOW())

then put this in column A
=IF(ISERROR(INDEX(FL,ROW()-2)),"",INDEX(FL,ROW()-2))

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?

 

[USAFRet]

OK. A MUCH better way to do that is to give the cell (or range of cells) a 'name'.

Click on Sheet 1, Cell B3. At the top left, you'll see a little reference box, with "B3" in it. Change that to MyRange.
That cell is now a named range in that workbook.

Anywhere in that workbook, you can say =MyRange. Whatever cell you put that in displays the value of Sheet 1, B3.

 

[USAFRet]

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.

 

[indyitguy]

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?

 

[USAFRet]

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.