How do I fix the column $C$but have the row number change

BRADX · May 14th, 2002, 02:52 PM

I was wondering if anyone knows how to fix the column fixed in a formula but have the row number change. I want to separate them so I can set a formula for the value in the row such as (C55-12) so the next cell is C43
Thanks

Jack in the UK · May 14th, 2002, 02:57 PM

suggest $C43 not $C$43

the $ anchors the column or row concerned remove one allows scrolling if dragged

HTH

Al Chara · May 14th, 2002, 03:11 PM

Please give an example of your formula, unless Jack's answer is sufficient.

Barry Katcher · May 14th, 2002, 03:26 PM

A $ before the column letter anchors the column and leaves the row as relative, A $ before the row number anchors the row and leaves the column relative. A $ before each makes both column and row absolute.

Go to the formula you want to change, hit F2 (Edit) and then hit F4 as many times as needed to achieve the proper configuration.

WayneTN · May 14th, 2002, 07:28 PM

One small elaboration......you could just click on the cell with the formula, then put the cursor on the cell address (either in that cell itself or on the formula bar), then hit F4 as many times as needed to get the right combination of $'s.

Yogi Anand · May 14th, 2002, 07:37 PM

Quote:

On 2002-05-14 13:52, BRADX wrote:
I was wondering if anyone knows how to fix the column fixed in a formula but have the row number change. I want to separate them so I can set a formula for the value in the row such as (C55-12) so the next cell is C43
Thanks

I hope you got a good feel for the relative and absolute aspect of a cell range from the helpful folks in earlier responses to you post. If you are looking for using the ability to use (C55-12) type calculations, you will also have to look at the INDIRECT function ...

For example
=INDIRECT("C"&(55-12))

gives me what is in cell C43

Hope This Helps

May 14th, 2002, 02:57 PM	#2
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,065	suggest $C43 not $C$43 the $ anchors the column or row concerned remove one allows scrolling if dragged HTH __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

May 14th, 2002, 03:26 PM	#4
Barry Katcher Board Regular Join Date: Feb 2002 Location: Dog Beach, Florida. Yeaahh! Posts: 4,038	A $ before the column letter anchors the column and leaves the row as relative, A $ before the row number anchors the row and leaves the column relative. A $ before each makes both column and row absolute. Go to the formula you want to change, hit F2 (Edit) and then hit F4 as many times as needed to achieve the proper configuration. __________________ Barry- Photo Restoration/Enhancement http://www.smiledogproductions.com click below for detour

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 14th, 2002, 02:52 PM	#1
BRADX New Member Join Date: May 2002 Posts: 8	I was wondering if anyone knows how to fix the column fixed in a formula but have the row number change. I want to separate them so I can set a formula for the value in the row such as (C55-12) so the next cell is C43 Thanks

May 14th, 2002, 03:11 PM	#3
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	Please give an example of your formula, unless Jack's answer is sufficient.

May 14th, 2002, 07:28 PM	#5
WayneTN Board Regular Join Date: May 2002 Location: Tennessee, USA Posts: 72	One small elaboration......you could just click on the cell with the formula, then put the cursor on the cell address (either in that cell itself or on the formula bar), then hit F4 as many times as needed to get the right combination of $'s.