Thanks:  0
Likes:  0

# Thread: Trying to use a cell value to reference another cell

1. For example, if i have the value 5 in B1 and i want to use that value to reference a cell in column C, ie the formula for A1 is something like:

=C(B1)

obviously that doesn't work, but i hope you get the just of it.
Thanks!

2. Hi

You need the INDIRECT function

=INDIRECT("C"&B1)

3. Go to bed, Dave.

4. Thank you Dave!

5. My pleasure kwaring

Tuggy, I can't sleep with both arms in a cast )

6. ## Re: Trying to use a cell value to reference another cell

Originally Posted by Dave Hawley
Hi

You need the INDIRECT function

=INDIRECT("C"&B1)
I am trying to put together a sheet that uses a lot of the INDIRECT function. Using a similar example to your above one =INDIRECT("C"&\$B\$1) is there a way to make the "C" changeable when dragging the function to apply to other cells? I want to apply the same formula to other columns, but it needs to reference the appropriate column rather than always "C". Same goes for rows.

I apologize for any poor terminology. I am fairly new to what to call things in Excel.

7. ## Re: Trying to use a cell value to reference another cell

Best to avoid INDIRECT() if possible, an alternative for the example given (that alters as the formula is coped across) would be:

=INDEX(C:C,\$B\$1)

You may need to give the details if this does not suffice.

8. ## Re: Trying to use a cell value to reference another cell

The portion of the formula I am having trouble with is:

=SUMSQ(COLUMNS(INDIRECT("A"&\$AW\$5):INDIRECT(\$AV\$5&"3"))-1,ROWS(INDIRECT("A"&\$AW\$5):INDIRECT(\$AV\$5&"3"))-1)

It works perfectly in one cell, but I would like to copy it to other cells easily which I can't do because of the portions that are in quotations. Is there a way to make that happen?

9. ## Re: Trying to use a cell value to reference another cell

A brief description of what you have in the cells in question, and the expected result as you drag the formula to other cells would greatly increase your chances of getting a solid solution.

10. ## Re: Trying to use a cell value to reference another cell

Originally Posted by FormR
A brief description of what you have in the cells in question, and the expected result as you drag the formula to other cells would greatly increase your chances of getting a solid solution.

=SQRT(SUMSQ(COLUMNS(INDIRECT("A"&\$AW\$5):INDIRECT(\$AV\$5&"3"))-1,ROWS(INDIRECT("A"&\$AW\$5):INDIRECT(\$AV\$5&"3"))-1))

AV5, AW5, AV6, and AW6 contain U, 3, U, and 27 respectively. The idea is that these cells can place a location from which the rest of the cells calculate their information. The location needs to be mobile.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•