Formula Macro?

[jlr123]

I have a formula which is like the following:
(I54-'AJF Amos'!I50-'ARF Aaron'!I50-'BNB Brittany'!I50)*M34

Each month I want to change the I (or whatever column it is on) to the next column (i,e. J) without having to go into the formula each month. Will not change the row. Any suggestions?
Thanks for your help.

 

[mintjulep]

You could use indirect.

Skip will soon explain why your spreadsheet design is almost certainly flawed.

 

[SkipVought]

Yes, Excel makes it so easy to paint yourself into a corner unwittingly,
because a sheet for each [pic yer poison] seems so right
and a column for each [pic anuther] seems like it is so easy to record.

It just make sense...

...until you actually need to do something with your data each [pic yer period].

Chopping up your data into separate sheets and recording your data in a summary format (like across months) presents difficulties preforming analysis and reporting.

"There is a way that seems right to a man..." Prov 16:25

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jlr123]

Skip,

You are not helpful at all. Maybe it's time for you to Re-Tire.

 

[Andrzejek]

My question would be: what do you keep in column I (that is OK for this month) that you need to change to column J (that is OK next month), and then point to column K (the following month)?

And, whatever you keep in these columns, can you keep that data in the same column no matter which month you need?



---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Only 30 years (+/-) of observation and practice.
As one surgeon said to the other, "Suit-yer self!"

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jlr123]

I cannot keep the data in one column.

 

[SkipVought]

Sure you could, if you also included the associated date in an other column!

Any time you have to CHANGE DATA in a formula or other code, you have a poor design.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jlr123]

Yes, it is a poor design, however it is the one I have for now. So no solution to my dilemma without re-creating the workbook?

 

[mintjulep]

Because you haven't really done a very good job of explaining you dilemma it's difficult for anyone to solve it for you without guessing.

However, as I suggested before, indirect seems like it would be useful to you.

 

[jlr123]

Thank you for your critique of my explanation. You have been so helpful. I will not be using Tek-Tips any longer if you will be responding to my threads.

 

[mintjulep]

You're welcome.

Thinking further, you might also consider using offset to solve your problem.

 

[SkipVought]

Set up a Named Range CELL where the user will enter a Column Number. I'll call it ThisMonCol

[tt]
=
(INDEX(54:54,1,ThisMonCol)
-INDEX('AJF Amos'!50:50,1,ThisMonCol)
-INDEX('ARF Aaron'!50:50,1,ThisMonCol)
-INDEX('BNB Brittany'!50:50,1,ThisMonCol)
)*M34
[/tt]


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein