×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Formula Macro?

Formula Macro?

Formula Macro?

(OP)
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.

RE: Formula Macro?

You could use indirect.

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

RE: Formula Macro?

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Macro?

(OP)
Skip,

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

RE: Formula Macro?

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.

RE: Formula Macro?

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Macro?

(OP)
I cannot keep the data in one column.

RE: Formula Macro?

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Macro?

(OP)
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?

RE: Formula Macro?

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.

RE: Formula Macro?

(OP)
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.

RE: Formula Macro?

You're welcome.

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

RE: Formula Macro?

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


=
(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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close