Calculating a Cell Location in Excel 2000 - Help?? 1

[jwpiii]

while I know that the ultimate solution to my project is to use Access, I have created an Excel spreadsheet for short term use while I work to develop the Access product.

In Excel, I am using a section of the sheet as a table like in Access, with the rows representing the records. I have an input from the user in a cell to represent which record they want to process. Lets say the user response is in cell AA1 and it is the value 85 representing the record contained in row 85. I need to be able to append the user input (85) to the column representing the data I want to work with is located at(example: row 85 column C to create C85)and then have the data that is located in that cell location (example: C85) moved to another cell in the spreadsheet (example: have the contents of Cell C85 be copied to cell BB1). I need this same function performed on twenty different cells.

I have been able to create the result of the cell location by using concatenate, but I can't convert the text value to an actual cell location. I am beginning to think it needs to be a VBA, but I have yet to write script for excel. Help!!!!!!! Many thanks in advance for assistance. John

 

[Zathras]

The way to do this is with the INDIRECT function. There are many variations on the theme, but here is the way I would do it. (I prefer using formulas that can be copied across the 20 cells instead of needing to tweak each one:

Set up the worksheet this way:

C85: 'I'm here!
D85: 'Me too!
E85: 'And me!
AA1: 85
AC1: 'C
AD1: 'D
AE1: 'E
AC2: =INDIRECT(AC1&$AA$1)
AD2: =INDIRECT(AD1&$AA$1)
AE2: =INDIRECT(AE1&$AA$1)

The formula in AC2 can be copied into AD2 and AE2.

 

[jwpiii]

Zathras,

Thank you!! Thank you!! Can't tell you how many hours I wasted trying to figure this out. Next time, I will abide by the fifteen minute rule: If you don't figure it out in fifteen minutes, ask for help!. I'm not to proud to admit my faults... Thanks again... John