As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options
Excelerate my spreadsheeting
Varinn
Vancouver, BCRegistered User regular
Vancouver, BCRegistered User regular
I am new to using Excel, and my boss has given me a new job that involves entering data every week into a weekly spreadsheet for the managers meetings. This logs how much time our department is spending on various types of tasks to see where the bulk of our manpower is going to. This involves some basic charts which he had already automated and setup, as well as some charts and graphs.
Our current setup is to use the pre existing template, and copy the entire contents of the blank to a new sheet which we then enter the data into such as the reporting week, and start putting the data into the premade spreadsheets. Because of the way the charts and graphs are setup I have to manually go into each individual chart and manually change the "select data" boxes for each item and manually select where it pulls the data from on each graph. Doing this every week results in me spending an hour going through chart after chart and modifying it to pull the data from the new sheet I create every Monday. Example, I have to change something like this
to be
What I really want is the option to modify the template, and as a result every sheet I make from it to look something like this.
Is this possible!?
Our current setup is to use the pre existing template, and copy the entire contents of the blank to a new sheet which we then enter the data into such as the reporting week, and start putting the data into the premade spreadsheets. Because of the way the charts and graphs are setup I have to manually go into each individual chart and manually change the "select data" boxes for each item and manually select where it pulls the data from on each graph. Doing this every week results in me spending an hour going through chart after chart and modifying it to pull the data from the new sheet I create every Monday. Example, I have to change something like this
=Template!$A$7
to be
='Nov 18 - 24'!$A$7
What I really want is the option to modify the template, and as a result every sheet I make from it to look something like this.
=CurrentSheet!$A$7
Is this possible!?
0
Posts
So you might have the current sheet set up as a named cell called "CurrentSheet"
And you would use INDIRECT() like so:
=INDIRECT( "'" & CurrentSheet & "'!$A$7" )
=INDIRECT("'" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,255) & "'!$A$7")