Excel Formula - referencing a variable cell

cdhoughton

Board Regular
Joined
Dec 5, 2003
Messages
170
Hi,

I am trying to implement the following formula.

I have a range, say C1:N1. I can find the value of a particular cell using an INDEX(MATCH....) formula fairly easily.

However I now need to perform the formula SUM(C1: x) where x is a variable cell determined by the user.

Any ideas on how to replace x with some code to pick up this, so the formula reads e.g. SUM(C1:D1), SUM(C1:H1). Note C1 is fixed.

Any help much appreciated!!
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
You didn't specify what kind of beast x is (position/number, cell address, etc.). Also, where is it entered by the user?
 

jcvoth

Board Regular
Joined
Feb 23, 2004
Messages
198
I tested this and it worked for me:

Sub sumcol()
endcol = InputBox("choose an ending column letter.") & "1"
Range("c2").Formula = "=SUM(c1:" & endcol & ")"
End Sub

The user chooses an ending letter (e.g. "G") and the code will sum everything from C1:G1.

Hope this helps!
-Jarrod
 

cdhoughton

Board Regular
Joined
Dec 5, 2003
Messages
170
Think I'm sorted now, cheers for the help!! Tried referencing a range between two different sheets :oops: !!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,204
Messages
5,594,826
Members
413,942
Latest member
Dhornsby21

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
Top