Setting a formula to use a "column" set in a cell

[jack109]

Hi

Is there a way to reference a column to be used by using a cell.

For example if G is in B2, then G used in the formula

eg. =SUM(G2:G12)

If B2's value was P then the formula would change to =SUM(P2:P12)

I've tried searching online but I'm finding it hard to word it without providing the above example

Cheers

 

[Joe4]

Build the string for your range reference ("B2:B12") like this:

B2 & "2:" & B2 & "12"

Then, you can wrap that in an INDIRECT function to tell Excel it is meant to be a range and not a string, and SUM it, i.e.

=SUM(INDIRECT(B2 & "2:" & B2 & "12"))

 

[saboh12617]

Hello,
You can use INDIRECT

=SUM(INDIRECT(B2 & "2:" & B2 & "12"))

 

[jack109]

Brilliant, is there a way that you can also set the rows to use , so if B3 was 7 and B4 was 15 it would tell excel using my above example that the range to use would be G7:G15

 

[Joe4]

Yes, follow the exact same process as I explained above. Sew together range references and hard-coded values.
Start of building a formula to build the range. Once that looks right (i.e. your formula returns "G7:G15"), wrap it in the INDEX formula and SUM it.

Give it a try - it will help you learn it, and post back with your attempt if you run into any issue.

 

[jack109]

Excellent, finally managed it - didn't realise it was that "simple". Thanks for the advice built it as a string first and when that looked right put into the formula and it worked

=SUM(INDIRECT(B2&B3&":"&B2&B4))

 

[Joe4]

Excellent, finally managed it - didn't realise it was that "simple". Thanks for the advice built it as a string first and when that looked right put into the formula and it worked

=SUM(INDIRECT(B2&B3&":"&B2&B4))

Excellent!

Now that you understand how it works, you will be able to set up more in the future!

 

[jack109]

Would it be the same sort of method if I just want to set a column in a formula that uses a single cell and not a range, so that B2 would just change the "G"

=IF($G10+$T$3=100,"YES","NO"

I am trying myself but wondering if I am on the right track

 

[Joe4]

Anytime you try to build a range dynamically, the formula will return a string value.
So Excel will NOT recognize your built formula as a range, until you wrap it in an INDIRECT function.
That is true regardless of whether your range is one cell, or a range of cells.

 

[jack109]

Start of building a formula to build the range. Once that looks right (i.e. your formula returns "G7:G15"), wrap it in the INDEX formula and SUM it.

INDEX or INDIRECT? as you mentioned INDEX here but the formula you give was INDIRECT.


Anyway I can get it working in one formula using a "helper" cell say to speak

ALERTS.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	COL	D
3
4
5
6				100									D6	no	yes
Sheet5

Cell Formulas
Range	Formula
M6	M6	=INDIRECT("B2")&ROW()
N6	N6	=IF((INDIRECT("B2")&ROW())=100,"yes","no")
O6	O6	=IF(INDIRECT(M6)=100,"yes","no")