Combining cells to create formula

[Riaan]

Hi All


Can someone please help with the following problem that I have got? I want to combine text and numbers from different cells to create a formula, but the result just comes out as the combination and not the desired result. In cell A1 I insert = and in cell A2 I insert Sheet2! And in A3 I insert B3. in cell C1 I insert =A1&A2&A3 the result then is =Sheet2!B3. That is correct, but I would like the cell to display Sheet2 cell B3.


Thank you in advance for your help

Riaan

 

[Hui]

=LEFT(A2,LEN(A2)-1)&" cell "&A3

 

[Riaan]

Hi Hui


Sorry, i should have clearer. I would like to see the value in cell B3 of sheet2

 

[Hui]

=INDIRECT(A2&A3)


But your better off to go

=INDIRECT(A2&"!"&A3)

so that in A2 you can just put Sheet2

 

[Riaan]

Hi Hui


Thank you that works. It does not acheave what i am looking for. If i type =Sheet2!B3 into C1, then i can drag the bottom right corner of cell C1 to auto fill as many cells as I want. Thus giving me the contents of the cells next to B3 on Sheet2. is this possible?

 

[Hui]

Riaan


The 2 approaches to retrieving data from Sheet2 are not at all compatible

The formula =INDIRECT(A2&A3) takes the text from Cells A2 and A3 to make a psuedo link to Sheet2!B3. Dragging this formula changes the internal links which is unlikely to give you the results you want, as it is very dependant on the values of Cells A2 & A3


Your formula =Sheet2!B3 is a direct link to Sheet2 B3

as such dragging like you said will allow the cell references to adjust to Sheet2!B4, B5 etc


Why can't you use =Sheet2!B3 in C1 ?

Why do you want links made up from other cells values?

There are times and places for both solutions

If you define what your trying to achieve there may even be other ways to tackle this one.

 

[Hui]

Riaan


The 2 approaches to retrieving data from Sheet2 are not at all compatible

The formula =INDIRECT(A2&A3) takes the text from Cells A2 and A3 to make a psuedo link to Sheet2!B3. Dragging this formula changes the internal links which is unlikely to give you the results you want, as it is very dependant on the values of Cells A2 & A3


Your formula =Sheet2!B3 is a direct link to Sheet2 B3

as such dragging like you said will allow the cell references to adjust to Sheet2!B4, B5 etc


Why can't you use =Sheet2!B3 in C1 ?

Why do you want links made up from other cells values?

There are times and places for both solutions

If you define what your trying to achieve there may even be other ways to tackle this one.

 

[Riaan]

Hui


I understand what you are saying. and i have tried every way I know of to get arround this problem.


What i am trying to achieve is. On Sheet2 i have entered a lot of data. it is in table form each with a unique name in top left corner. meaning the name for each data set is in colum A. each data set is 19 colums wide and 45 rows high. I have over 100 of the data sets one under the other. What i do in sheet1 is to use the match function to find the unique name on sheet2 and return the line number.


That is why i want to ad cells together. I know that the answer is in colum A on Sheet2, by then adding the variable that the match function gives me. I can then drag the bottom right corner and fill the cells next to, and below my function cell to get the entire data set.

 

[Hui]

In C1 put

=INDIRECT($A$2&"!"&"R"&ROW()&"C"&COLUMN()-2,False)

Drag away

Change Sheet name in A2, to suit


Now if you want to lookup a Value on Sheet2, Col A and use that to offset your data lookup

in C1: =INDIRECT($A$2&"!"&"R"&ROW()+MATCH($A$3,Sheet2!$A:$A,0)-1&"C"&COLUMN()-2,False)

Where the Value to Lookup is in A3

 

[Riaan]

Hi Hui


Thank you for all your help. I am an extreme novice at excel, but this does not seem to work either or I am using it wrong. Is it possible that I can e-mail you the workbook, that you can see what I am referring to?

 

[Hui]

Click on the Words Excel Ninja under my Avatar

my email is at the bottom of the page