Solved

Adding multiple workbooks - Dynamic

Posted on 2013-06-25
4
133 Views
Last Modified: 2013-06-25
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
Comment
Question by:Jenedge73
  • 2
4 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39274702
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
 

Author Comment

by:Jenedge73
ID: 39276201
I've requested that this question be deleted for the following reason:

I can't do what I want
0
 
LVL 12

Accepted Solution

by:
telyni19 earned 500 total points
ID: 39276061
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
 

Author Closing Comment

by:Jenedge73
ID: 39276202
That was exactly what I wanted.  Thanks
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question