concatenating Row and Col info in a formula

[SamS]

Probably too simple but I am stuck. I have a combo box that returns the necessary value eg 4. Now by a strange quirk I have found that if I mutipy my answer by 4 and add 20 it will always give me a reference to the correct row of cells which I would like to graph, my dilema is how do I concatenate a Column heading with the result to return a cell address in a formula. ="C" & A1 returns me C24 but if I try to use this it returns a #VALUE!

 

[VoG]

Try

=INDIRECT("C" & A1)

 

[Lewiy]

Use INDIRECT to return the value of a cell which is referenced by a string:

=INDIRECT("C"&A1)

This will return the value of cell C24 (if A1 = 24)

 

[SamS]

Vog II and Lewiy, thanks for your responses, I did try Indirect but it gave me 39246 as the answer which is definately not anywhere on my spreadsheet

 

[SamS]

Had a nasty thought that 39264 was a date, tried reformatting the cell as a number but still the same issue. Put data into completely new spread sheet and used the formula and it worked.

tnanks

 

[Lewiy]

39264 is 1st July 2007 if that helps!

 

[SamS]

Thanks for the info Lewiy also makes sense, seems unless I can resolve the formatting issue (started new topic) I may just have to copy data into new spreadsheet. Cells that have been formatted as comments, dates etc will not reformat.