• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 155
  • Last Modified:

Adding multiple workbooks - Dynamic

I have a payroll workbook that I'm working on.  Every two weeks I add another worksheet by copying the template.  Is there a way that my total worksheet could be continually updated to reflect the new worksheet, preferably without using a vba.
Thanks
0
Jenedge73
Asked:
Jenedge73
  • 2
1 Solution
 
gowflowCommented:
I don't think so as your adding sheets it would be difficult or I don't see how without VBA.
If you change your mind and accept VBA then pls provide a sample workbook and it would be no sweat to give you a solution.
gowflow
0
 
Jenedge73Author Commented:
I've requested that this question be deleted for the following reason:

I can't do what I want
0
 
telyni19Commented:
If your new sheets always follow a particular date format, then you could construct a summary sheet that uses calculated references to construct the information you need, using IFERROR statements to hide/recast values that don't exist yet. This is possible using only Excel formulas without VBA.

See attached for a simple example that uses a constructed date format to anticipate what the next two sheets will be called and automatically pull the values once they exist in the file. A total field at the top calculates the total for all sheets. Any future dates appear as zero by default.

Formulas:
To construct a month/day sheet name from a date:
=TEXT(A4,"mmm")&DAY(A4)
To construct an indirect sheet reference (note the exclamation point concatenated after the sheet name and before the cell reference):
=INDIRECT(B4&"!B2:B100")
BiweeklySummary.xlsx
0
 
Jenedge73Author Commented:
That was exactly what I wanted.  Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now