×
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

SUMIFS VARIABLE SHEET NAME

SUMIFS VARIABLE SHEET NAME

SUMIFS VARIABLE SHEET NAME

(OP)
Hi, I'm trying to create a macro which uses SUMIFS function. I want this macro to be used as it is every month without changing anything. The problem is, the sheet name varies every month.

Here's the sample of my formula:

Range("U2").Formula = "=SUMIFS('February 2018'!U:U,'February 2018'!L:L,$L2,'February 2018'!O:O,$O2, 'February 2018'!AL:AL,'Filter 2'!$AL2)"

The sheet 'February 2018' sheet name varies every month.

Is there a way to set it so it would reflect the name as the previous month every month?

RE: SUMIFS VARIABLE SHEET NAME

Hi,

Quote:

The problem is, the sheet name varies every month.

Your problem is the result of a bad workbook design. Having similar data in multiple sheets is a very very poor design choice. It is referred to as NON-NORMALIZED data. It seems right, because from a user’s point of view, the argument goes, all they have to do is select the week or month or year tab in order to view that period’s data. However, if ALL your data were in ONE SINGLE SHEET with an added column for AGGREGATION_DATE, the user could easily Filter by year/month AND you could perform your SUMIFS() aggregation quite simply.

So I’d put my efforts into normalizing your data by consolidating into one table. I’ve had to do this on several occasions. Its well worth the effort.

Skip,

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

RE: SUMIFS VARIABLE SHEET NAME

BTW, welcome to Tek-Tips.

If you intend on keeping you current workbook structure, although I strongly urge against it, here’s a solution.

I’m assuming a cell on one of your sheets Named Mnth that contains the new sheet name.

You might consider making this cell a Data > Validation > LIST

CODE

Range("U2").Formula = "=SUMIFS('” & [Mnth] & “'!U:U,'” & [Mnth] & “'!L:L,$L2,'” & [Mnth] & “'!O:O,$O2, '” & [Mnth] & “'!AL:AL,'Filter 2'!$AL2)" 

Skip,

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

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!

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