Variable Cell Range

balki

New member
Joined
Aug 21, 2014
Messages
4
Reaction score
0
Points
0
Hi,

Looking for a formula that will change the cell range dynamically based on a variable in another cell.

For Example: I have numbers 1 - 10 in A1:A10
@ c4 =sum(A1:A4)
@ e10 = 4

If I change the number in E10 from 4 to 7, then at C4 the sum should automatically change to sum(A1:A7)

-Balki
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Try

=SUM(A1:INDEX(A:A,E10))
 

balki

New member
Joined
Aug 21, 2014
Messages
4
Reaction score
0
Points
0
Hello Bob,

I used the Index in below:

COUNTIF(Data!C4:INDEX(C:C,S3),"Pending")

What I am trying to achieve is get the count from Sheet name "Data" if the text = "Pending".
The range C4 to Cx will be decided by the value in S3

Regards,
Balki
 

balki

New member
Joined
Aug 21, 2014
Messages
4
Reaction score
0
Points
0
Hello Bob,

Tx. Got it right this time. It should have been COUNTIF(Data!C4:INDEX(Data!C:C,Data!S3),"Pending")

Regards,
Balki
 
Top