Incrementing a Formula by One

[martinpgibson]

Hi there,

I have an array formula in A1:

{=IFERROR(INDEX(leave_dates[Status],MATCH(1,([@NAME]=leave_dates[Name])*(VALUE(anuual_leave[[#Headers],[69]])>=leave_dates[Start Date Mod])*(VALUE(anuual_leave[[#Headers],[69]])<=leave_dates[Finish Date Mod]),0),),"")}

And what I need to do is put this formula into the consequent columns B,C,D,E etc. but as each column moves across I need the "69" number in the formula to increase by one. So the formula in B1 would be as above but with the number 70 instead of 69 and so on.

I really appreciate your help because I need to copy this formula over to many many columns!

[FDibbins]

Try using the COLUMN() function...
[69+column(A1)]

[MarvinP]

Hi Martin,

The way to accomplish this is to have a "Columns()" formula involved. In your B Column put this in place of your "69"

=68+COLUMNS($B:B)

Then when you drag your formula to the right it will become 70, 71...

[FDibbins]

Another option would be to use COLUMN(BQ1) instead of 69. This will increase as it is copied across

[:) Sixthsense :)]

I believe you can simple replace this whole reference with the suggested solution.

VALUE(anuual_leave[[#Headers],[69]])


There is no need to include the table references when you convert it as value and also using the single Column Header reference

68+COLUMNS($A:A)

[martinpgibson]

Hi Everyone,

Thanks for all your answers. I should have mentioned initially that I had the column() formula going initially but it doesn't work in this application because it is part of a query and when the query refreshes the cell references were tripping out giving a REF error. So what I am trying to do is create a formula that doesn't have any cell references that way it wont trip out. I was thinking of having maybe some macro way of putting the formula in the consequent columns and incrementing the value. Any ideas?

Thanks,

[FDibbins]

I think its time you showed us a sample workbook of what you are working with, and what you want

[:) Sixthsense :)]

Thanks for all your answers. I should have mentioned initially that I had the column()

If I am not wrong using =COLUMN() will never result Error since it will get the current column #.

Or

Check whether Indirect() will help you...

=COLUMN(INDIRECT("A:A"))