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!!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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: !!!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,831
Messages
5,855,889
Members
431,772
Latest member
dannyboi1

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