Trying to use a cell value to reference another cell

kwaring

New Member
Joined
Mar 14, 2002
Messages
49
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!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top