M
Metallo
Hi,
The reason why I point you to the code you gave me on August 6th is because
most probably you will need to look at it to fully understand what I’m
talking about.
I summarize the picture as it is today and then try to describe what I need,
basically two questions:
I have a folder that contains:
11 WBs (10 referring to different units and 1 consolidating the 10 units)
They all have same format and functions.
Every WB has got many WSs that include a function that allows selecting a
month (1 - 12) in any of the WSs and the result is the YTD at the given
month. (Yr code of August 6th).
This applies for the consolidated WB as well.
In the consolidated WB I have a table that summarizes all the results of the
10 single units (10 WBs).
I have applied the same code to that table, so if I enter 6 I get the
consolidated YTD for June. But of course, in this table I only get the
Totals, because they are in the same WB.
For example:
In my Folder I have all the WBs, where 6 is the month they have been saved
when last closed.
WB unit A – Value 3
WB unit B – Value 5
WB unit C – Value 7
Etc.…
If I open them and change to, say 5, then I will get:
WB unit A – Value 2
WB unit B – Value 3
WB unit C – Value 5
Etc.…
What happens in the table that summarizes all the units in the Consolidated
WB if I enter month 6, and the single units WBs have been saved and closed in
month 5?
WB Consolidated
WB unit A – Value 2
WB unit B – Value 3
WB unit C – Value 5
Total – value 15
So, I will only get the Total to change but the single units remain at the
value they’ve been closed and saved in the Folder, that is in this case, 5.
So, what I should need is, when I input the number in the table WS in
Consolidated, is to get the month number to change in the single units as
well.
So, if I enter 6 in the Consolidated WB -> Not only I get all the WSs
included into this WB to switch to 6, but also all the 10 single units WBs
switching to 6, so that I can visualize the changes in the consolidated
table that summarizes all the units.
This function should occur regardless if the 10 unit WBs are open or not
(most probably they would be closed).
I was thinking to do this by recording a macro, but I would end up in never
ending macros that open/close all the WBs every time.
-------------------------
Second question:
In the consolidated WBs, in addition to the WSs, there are a good number of
Charts.
When I enter the month number to check the YTD, the charts change accordingly.
When I do presentations, I'd like to see the reference period on the Chart
as well, so that the audience knows that the data shown in the Chart refer to
month 6 or 8 and so on.
Is there a way to "visually" transfer the reference month to the Charts?
I do apologize for being that long, but it is not easy to explain such a
complex question in writing.
Thank you for your help.
Alex
The reason why I point you to the code you gave me on August 6th is because
most probably you will need to look at it to fully understand what I’m
talking about.
I summarize the picture as it is today and then try to describe what I need,
basically two questions:
I have a folder that contains:
11 WBs (10 referring to different units and 1 consolidating the 10 units)
They all have same format and functions.
Every WB has got many WSs that include a function that allows selecting a
month (1 - 12) in any of the WSs and the result is the YTD at the given
month. (Yr code of August 6th).
This applies for the consolidated WB as well.
In the consolidated WB I have a table that summarizes all the results of the
10 single units (10 WBs).
I have applied the same code to that table, so if I enter 6 I get the
consolidated YTD for June. But of course, in this table I only get the
Totals, because they are in the same WB.
For example:
In my Folder I have all the WBs, where 6 is the month they have been saved
when last closed.
WB unit A – Value 3
WB unit B – Value 5
WB unit C – Value 7
Etc.…
If I open them and change to, say 5, then I will get:
WB unit A – Value 2
WB unit B – Value 3
WB unit C – Value 5
Etc.…
What happens in the table that summarizes all the units in the Consolidated
WB if I enter month 6, and the single units WBs have been saved and closed in
month 5?
WB Consolidated
WB unit A – Value 2
WB unit B – Value 3
WB unit C – Value 5
Total – value 15
So, I will only get the Total to change but the single units remain at the
value they’ve been closed and saved in the Folder, that is in this case, 5.
So, what I should need is, when I input the number in the table WS in
Consolidated, is to get the month number to change in the single units as
well.
So, if I enter 6 in the Consolidated WB -> Not only I get all the WSs
included into this WB to switch to 6, but also all the 10 single units WBs
switching to 6, so that I can visualize the changes in the consolidated
table that summarizes all the units.
This function should occur regardless if the 10 unit WBs are open or not
(most probably they would be closed).
I was thinking to do this by recording a macro, but I would end up in never
ending macros that open/close all the WBs every time.
-------------------------
Second question:
In the consolidated WBs, in addition to the WSs, there are a good number of
Charts.
When I enter the month number to check the YTD, the charts change accordingly.
When I do presentations, I'd like to see the reference period on the Chart
as well, so that the audience knows that the data shown in the Chart refer to
month 6 or 8 and so on.
Is there a way to "visually" transfer the reference month to the Charts?
I do apologize for being that long, but it is not easy to explain such a
complex question in writing.
Thank you for your help.
Alex