• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 152
  • 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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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