changing column number in formula automatically

L

Legacy 69493

Guest
Thanks for your help in creating an IF statement but now I would like to change the column numbers automatically and have the formula in every column.

ie. =If(O32="G",N32*0.06,"")
then I want this to change to
=IF(O33="G",N33*0.06,"")

Is there a way of having the column numbers change automatically in every column so that I don't have to enter this to over 200 columns?

Thanks
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
That's changing row numbers isn't it? Do you want them to increase when you copy the formula across columns?

=IF(INDEX($O:$O,COLUMN(A1)-COLUMN($A1)+ROW(O32))="G",INDEX($N:$N,COLUMN(A1)-COLUMN($A1)+ROW(N32))*0.06,"")
 
L

Legacy 69493

Guest
Thank you for your suggestions.

Yes it is the row numbers that I want to advance as I am trying to get a bunch of items to calculate GST if they have GST applied. I have a column with G in it to state wether it does or not.

Coping does not get it to advance to row numbers with it and the other formula from andrew I don't understand enough but when I tried it it didn't work.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

It worked when I tried it. In what way didn't it work for you?
 

Pekkavee

Well-known Member
Joined
May 25, 2004
Messages
1,218
Do you have any "$" signs in your formula?

Take them away.

Then it will work when copying.

Pekka
:eek: :eek: :eek:
 
L

Legacy 69493

Guest

ADVERTISEMENT

Sorry taking out the "$" didn't work either.

How it didn't work was it calculated nothing in all the rows including when their was a "G" in the line to calculate GST.

Thanks for everyones suggestions.
 

Pekkavee

Well-known Member
Joined
May 25, 2004
Messages
1,218
Maybe you have an extra space in those cells.

G + space.

Try just put a "G" in there
If it works, then you can use TRIM formula to get rid of spaces.

Pekka
:eek: :eek: :eek:
PS. What are your formulas in those other cells after taking "$" signs away from the first cell and copying it to other cells.?
 
L

Legacy 69493

Guest
I tried this formula.

=IF(INDEX(O:O,COLUMN(A1)-COLUMN(A1)+ROW(O32))="G",INDEX(N:N,COLUMN(A1)-COLUMN(A1)+ROW(N32))*0.06,"")

I don't think their are extra spaces I cut and pasted andrews formula.
 

Pekkavee

Well-known Member
Joined
May 25, 2004
Messages
1,218
I tried this formula.

=IF(INDEX(O:O,COLUMN(A1)-COLUMN(A1)+ROW(O32))="G",INDEX(N:N,COLUMN(A1)-COLUMN(A1)+ROW(N32))*0.06,"")

I don't think their are extra spaces I cut and pasted andrews formula.

I think your own formula should work in this case when copied without "$" signs

Pekka
:eek: :eek: :eek:
 

Forum statistics

Threads
1,136,266
Messages
5,674,727
Members
419,523
Latest member
Urnovio

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