Automatically update charts for reporting month

[brijendraydv]

Hi Experts,


Iam attaching my file in this link:http://speedy.sh/mWPtk/Scorecard.xls


I want that in the tables sheet if i give the cell reference for april month from sheet EMEA range("AW4") for ex.

the chart(first chart) in EMEA sheet for net core prod gets updated for April only.

so i want this to happend automatically every month.Is there any formula to do this?

if you see tables sheet in EMEA Delivery performance in %ship to req i have written formula to get updated automaticallly every month ie IF(EMEA!AW23="#NA",NA(),EMEA!AW23).

 

[Faseeh]

Hi brijendraydv,


You don't want to display zero values in your chart?? You can wrap your formula in IFERROR() or IF(ISERROR(),NA()):


Following are some examples from your sheet:

[pre]

=IFERROR(AW5/AW18,NA())
=IFERROR(AW7/AW18,NA())
=IFERROR(AW13/AW18,NA())
=IFERROR(AW16/AW18,NA())

[/pre]
If it is something else then kindly tell me.


Regards,

Faseeh

 

[brijendraydv]

Hi Fasseh,


Thanks for the reply but where should i write your formula (whcih sheet)


Regards,

 

[Faseeh]

Hi brijendraydv,


I modified formulas in sheet "Table" now as you update the sheet EMEA, the changes will be reflected in the other sheet. I think you were reluctant to apply formula to the entire range of Table bcz that might have given you Zeros in Your Graph?? Replacing an error msg with NA() makes it unreadable for graphs and these points are not plotted. Here is the file:


http://dl.dropbox.com/u/60644346/Scorecard.xls


If not appropriate then inform me.


Regards,

Faseeh


[This marked my 500th post :D Yeah]

 

[brijendraydv]

Hi Faseeh,


thanks, but your formula is working when i put the values manually in tables sheet and the chart is getting updated in EMEA sheet which is good but here what my requirement is that when the values in EMEA region gets updat.ed for example in cell AW4 then the chart should gets updated automaticaly where chart source data is in table sheet where i have given cell reference from EMEA sheet.for clearity you can see other tables like

EMEA Delivery performance cell "Y8" formula.hope you understood my req.


Regards,

 

[Montrey]

Use a dynamic range with the offset formula. Have you tried this yet?


This will update the graph automatically

 

[Faseeh]

Hi brijendraydv,


The formula you refered to in Y8 is a simple if formula. Actually i am unable to understand the exact requirement that:


1. which table you want to update from which table?

2. Do you want to update just data to as Montrey said you also want table to be "Expendable" as well?


Please clarify.


Regards,

Faseeh

 

[brijendraydv]

Hi Faseeh,


Answer to your points


1)which table you want to update from which table?- I want to update EMEA Productivity table in tables sheet and that updateion should happen automatically by writing some formula.I want that formula and once that formula is entered the chart should autoamtically update in EMEa sheet.

for an example you can see EMEA Delivery performance table in "tables" sheet.

Hope im clear noe.


Thanks,

 

[Faseeh]

Hi brijendraydv,


Sorry for this day late reply.


See this sample sheet: http://dl.dropbox.com/u/60644346/Scorecard.xlsx


I have experimented with only one series (1st) series of EMA Productivity table, I made it expendable by using a formula so that when ever you add something to the right of that table the series will be updated automatically on the graph. Is this that you wanted?


I think you are making things complicated for yourself as and a Named Series defined by OFFSET() could do the trick but i would prefer just to select the series and extend its range by using cursor/mouse.


Regards,

Faseeh