constructing a cell reference from two strings?

Z

z.entropic

In the following specific case, if I have the number 3 in B3 and the letter C in A2, how do I construct C3 in an arbitrary cell which would be an actual reference to cell C3? =B3&A2 gives C3, but it's a string, not a reference... The ADDRESS(row_num,column_num,abs_num,a1,sheet_text) function gives a string, too. I'm missing the conversion trick..

z.entropic
 
B

Bob Phillips

=INDIRECT(B3&A2)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

z.entropic said:
In the following specific case, if I have the number 3 in B3 and the
Click to expand...
letter C in A2, how do I construct C3 in an arbitrary cell which would be an
actual reference to cell C3? =B3&A2 gives C3, but it's a string, not a
reference... The ADDRESS(row_num,column_num,abs_num,a1,sheet_text) function
gives a string, too. I'm missing the conversion trick...
 
D

Don Guillett

try this where c is in b2 and 3 in b3
=INDIRECT(B2&B3)

--
Don Guillett
SalesAid Software
(e-mail address removed)
z.entropic said:
In the following specific case, if I have the number 3 in B3 and the
Click to expand...
letter C in A2, how do I construct C3 in an arbitrary cell which would be an
actual reference to cell C3? =B3&A2 gives C3, but it's a string, not a
reference... The ADDRESS(row_num,column_num,abs_num,a1,sheet_text) function
gives a string, too. I'm missing the conversion trick...
 
Z

z.entropic

Well, this solution worked in simple circumstances, but it failed when I referred to two different worksheets

=Interp('Data'!$N$3:INDIRECT("$N$"&Main!$B$4),'Data'!$I$3:$I$4400,F4

( I have an entry, 4400, in cell 'Main'!B4; Interp is an interpolation function from XlXtrFun.xll

z.entropic
 
V

Vasant Nanavati

Could it be because you didn't do it right? <g>

=Interp(INDIRECT("'Data'!$N$3:$N$"&Main!$B$4),'Data'!$I$3:$I$4400,F4)

--

Vasant



z.entropic said:
Well, this solution worked in simple circumstances, but it failed when I
Click to expand...
referred to two different worksheets:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help calculating probabilities 7
Looking to added up based on a character from cell range 1
How to get cell coordinates? 3
Retaining cell reference in destination, when sorting source file 3
I want multiple formulas to always extract from same cell(s) 1
get a cell reference as a result of an If Function 1
Write formula for simple copy and paste to another cell 1
Setting a cell with a value as a reference for another function 1

Top