Increasing Column Number using IF formula when dragging across

[VanessaDowning]

Hi, I have a formula =IF($K$11=1,$J$11,"") in column AD

When I drag the formula to the right, I want to "1" to increase in value by 1.

So, as the formula is dragged to the right, the formula changes to:

1. =IF($K$11=2,$J$11,"") in column AE, then
2. =IF($K$11=3,$J$11,"") in column AF, then
3. =IF($K$11=4,$J$11,"") in column AG, etc, etc

Is there an easy way to make this work please? I just can't figure it out....

Thank you

 

[mohadin]

Hi
May be
In column AD

=IF($K$11=COLUMN()-28,$J$11,"")

 

[VanessaDowning]

Hi
May be
In column AD

=IF($K$11=COLUMN()-28,$J$11,"")

Hi Mohadin,

Thanks for your reply.

Sorry I think that I didn't explain it well.

The value in column K will be manually entered. I've shown a screenprint below - not sure if that will help please?

Thanks again.

Kind Regards,

Vanessa

 

[AlanY]

try this in AD14

=IF($K14=(COLUMN(AD1)-COLUMN($AC$1)),$J14,"")

 

[VanessaDowning]

try this in AD14

=IF($K14=(COLUMN(AD1)-COLUMN($AC$1)),$J14,"")

Hi Alan Y,

Sorry nope it doesn't work.....it's driving me *******s....the value which is driving the result is the value in column K (if that helps...?)

Thanks again

 

[AlanY]

it works for me

Book1
	J	K																		AC	AD	AE	AF
1
2
3
4
5
6
7
8
9
10
11
12
13
14	10	1																			10
Sheet1

Cell Formulas
Range	Formula
AD14:AF14	AD14	=IF($K14=(COLUMN(AD1)-COLUMN($AC$1)),$J14,"")

Book1
	J	K																		AC	AD	AE	AF
1
2
3
4
5
6
7
8
9
10
11
12
13
14	20	2																				20
Sheet1

Cell Formulas
Range	Formula
AD14:AF14	AD14	=IF($K14=(COLUMN(AD1)-COLUMN($AC$1)),$J14,"")

Book1
	J	K																		AC	AD	AE	AF
1
2
3
4
5
6
7
8
9
10
11
12
13
14	33	3																					33
Sheet1

Cell Formulas
Range	Formula
AD14:AF14	AD14	=IF($K14=(COLUMN(AD1)-COLUMN($AC$1)),$J14,"")

 

[AlanY]

is this what you're after?

Book1
	J	K																			AD	AE	AF
1
2
3
4
5
6
7
8
9
10
11
12
13
14	10	1																			10
15	20	2																				20
16	30	3																					30
Sheet1

Cell Formulas
Range	Formula
AD14:AF16	AD14	=IF($K14=(COLUMN(AD1)-COLUMN($AC$1)),$J14,"")

 

[VanessaDowning]

it works for me

Oh darn - it still isn't working for me....no idea what I'm doing wrong.....??? I've formatted Column J to be currency and Column K to be as a number, but it's still not working.....

 

[VanessaDowning]

Oh darn - it still isn't working for me....no idea what I'm doing wrong.....??? I've formatted Column J to be currency and Column K to be as a number, but it's still not working.....

DOH!!!! I've just realised that I'm trying to copy it into cell AD11, not cell AD14....
It works!!!!
Well done, AlanY - you're brilliant!

 

[AlanY]

glad that you got it sorted

DOH!!!! I've just realised that I'm trying to copy it into cell AD11, not cell AD14....
It works!!!!
Well done, AlanY - you're brilliant!