| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Issue with dynamic chart mapping out blank info
I have created a rolling 12 month graph which looks at the data on a table
and determines which is the most up to date data to use and constantly will update itself with the new month and remove the oldest month when new data is entered......however, the data that I have is a lookup to another spreadsheet which I have used a formula to retrieve, problem is that the dynamic chart is assuming that because there is a formula in the table that it needs to be charted even when the formula returns a null value. My excel skills are average to say the best, so in laymans terms would be helpful :-) Thanks so much |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Issue with dynamic chart mapping out blank info
Hi,
You should show us the formula you are using. But lets assume its something like =IF(VLOOKUP(A1,Sheet2!A1:D100,3,FALSE)="",NA(),VLO OKUP(A1,Sheet2!A1:D100,3,FALSE)) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Nessygirl" wrote: I have created a rolling 12 month graph which looks at the data on a table and determines which is the most up to date data to use and constantly will update itself with the new month and remove the oldest month when new data is entered......however, the data that I have is a lookup to another spreadsheet which I have used a formula to retrieve, problem is that the dynamic chart is assuming that because there is a formula in the table that it needs to be charted even when the formula returns a null value. My excel skills are average to say the best, so in laymans terms would be helpful :-) Thanks so much |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Issue with dynamic chart mapping out blank info
Hi Shane, thanks for this, I am not using a VLOOKUP - would that be a better
idea than the formula I am using: =IF('[Sheet'!AC8=0," ",'Sheet'!AC8), which does work and returns a blank cell, the issue comes when I am charting and it updates thinking that there is data in there due to the formula. Does this make sense?!? Regards -- Nessygirl "I really hate this machine, I wish that they would sell it, it never does what I want, only what I tell it" "Shane Devenshire" wrote: Hi, You should show us the formula you are using. But lets assume its something like =IF(VLOOKUP(A1,Sheet2!A1:D100,3,FALSE)="",NA(),VLO OKUP(A1,Sheet2!A1:D100,3,FALSE)) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Nessygirl" wrote: I have created a rolling 12 month graph which looks at the data on a table and determines which is the most up to date data to use and constantly will update itself with the new month and remove the oldest month when new data is entered......however, the data that I have is a lookup to another spreadsheet which I have used a formula to retrieve, problem is that the dynamic chart is assuming that because there is a formula in the table that it needs to be charted even when the formula returns a null value. My excel skills are average to say the best, so in laymans terms would be helpful :-) Thanks so much |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Issue with dynamic chart mapping out blank info
" " is a space
Blank should be "" (without a space) =IF('[Sheet'!AC8=0,"",'Sheet'!AC8) OR =IF('[Sheet'!AC8=0,NA(),'Sheet'!AC8) If this post helps click Yes --------------- Jacob Skaria "Nessygirl" wrote: Hi Shane, thanks for this, I am not using a VLOOKUP - would that be a better idea than the formula I am using: =IF('[Sheet'!AC8=0," ",'Sheet'!AC8), which does work and returns a blank cell, the issue comes when I am charting and it updates thinking that there is data in there due to the formula. Does this make sense?!? Regards -- Nessygirl "I really hate this machine, I wish that they would sell it, it never does what I want, only what I tell it" "Shane Devenshire" wrote: Hi, You should show us the formula you are using. But lets assume its something like =IF(VLOOKUP(A1,Sheet2!A1:D100,3,FALSE)="",NA(),VLO OKUP(A1,Sheet2!A1:D100,3,FALSE)) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Nessygirl" wrote: I have created a rolling 12 month graph which looks at the data on a table and determines which is the most up to date data to use and constantly will update itself with the new month and remove the oldest month when new data is entered......however, the data that I have is a lookup to another spreadsheet which I have used a formula to retrieve, problem is that the dynamic chart is assuming that because there is a formula in the table that it needs to be charted even when the formula returns a null value. My excel skills are average to say the best, so in laymans terms would be helpful :-) Thanks so much |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Issue with dynamic chart mapping out blank info
ok, changed the formula to incorporate "" not " ", formula works (of course),
but with the same result of the charts extending to the end of the formula set rather than where the data ends........really appreciate your answers though. I have defined names for the charts, perhaps I am doing something wrong there.... Data being charted: =OFFSET('sheet'!$A$3,0,COUNTA('sheet'!$3:$3)-1,1,-MIN('sheet'!MONTHS,COUNTA('sheet'!$3:$3)-1)) Months: ='sheet'!$A$1 Date range: =OFFSET('sheet'!$A$2,0,COUNTA('sheet'!$3:$3)-1,1,-MIN('sheet'!MONTHS,COUNTA('sheet'!$3:$3)-1)) as you can probably tell I am not the most excel savvy person :-) -- Nessygirl "I really hate this machine, I wish that they would sell it, it never does what I want, only what I tell it" "Jacob Skaria" wrote: " " is a space Blank should be "" (without a space) =IF('[Sheet'!AC8=0,"",'Sheet'!AC8) OR =IF('[Sheet'!AC8=0,NA(),'Sheet'!AC8) If this post helps click Yes --------------- Jacob Skaria "Nessygirl" wrote: Hi Shane, thanks for this, I am not using a VLOOKUP - would that be a better idea than the formula I am using: =IF('[Sheet'!AC8=0," ",'Sheet'!AC8), which does work and returns a blank cell, the issue comes when I am charting and it updates thinking that there is data in there due to the formula. Does this make sense?!? Regards -- Nessygirl "I really hate this machine, I wish that they would sell it, it never does what I want, only what I tell it" "Shane Devenshire" wrote: Hi, You should show us the formula you are using. But lets assume its something like =IF(VLOOKUP(A1,Sheet2!A1:D100,3,FALSE)="",NA(),VLO OKUP(A1,Sheet2!A1:D100,3,FALSE)) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Nessygirl" wrote: I have created a rolling 12 month graph which looks at the data on a table and determines which is the most up to date data to use and constantly will update itself with the new month and remove the oldest month when new data is entered......however, the data that I have is a lookup to another spreadsheet which I have used a formula to retrieve, problem is that the dynamic chart is assuming that because there is a formula in the table that it needs to be charted even when the formula returns a null value. My excel skills are average to say the best, so in laymans terms would be helpful :-) Thanks so much |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Issue with dynamic chart mapping out blank info
I am still having issues with this in that it is still charting till March
2013. I believe that this is caused by the fact that it sees a formula in the last 12 months, rather than looking to see if there is actual data to be charted....so essentially it is charting up to the last lot of data (June 2008 to May 2009), however attributing the data to the last 12 months (April 2012 to March 2013).....Please help..... :-( I can email the spreadsheet if required. Regards Nessygirl -- Nessygirl "I really hate this machine, I wish that they would sell it, it never does what I want, only what I tell it" "Jacob Skaria" wrote: " " is a space Blank should be "" (without a space) =IF('[Sheet'!AC8=0,"",'Sheet'!AC8) OR =IF('[Sheet'!AC8=0,NA(),'Sheet'!AC8) If this post helps click Yes --------------- Jacob Skaria "Nessygirl" wrote: Hi Shane, thanks for this, I am not using a VLOOKUP - would that be a better idea than the formula I am using: =IF('[Sheet'!AC8=0," ",'Sheet'!AC8), which does work and returns a blank cell, the issue comes when I am charting and it updates thinking that there is data in there due to the formula. Does this make sense?!? Regards -- Nessygirl "I really hate this machine, I wish that they would sell it, it never does what I want, only what I tell it" "Shane Devenshire" wrote: Hi, You should show us the formula you are using. But lets assume its something like =IF(VLOOKUP(A1,Sheet2!A1:D100,3,FALSE)="",NA(),VLO OKUP(A1,Sheet2!A1:D100,3,FALSE)) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Nessygirl" wrote: I have created a rolling 12 month graph which looks at the data on a table and determines which is the most up to date data to use and constantly will update itself with the new month and remove the oldest month when new data is entered......however, the data that I have is a lookup to another spreadsheet which I have used a formula to retrieve, problem is that the dynamic chart is assuming that because there is a formula in the table that it needs to be charted even when the formula returns a null value. My excel skills are average to say the best, so in laymans terms would be helpful :-) Thanks so much |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic Chart Update Issue | Excel Discussion (Misc queries) | |||
| Dynamic Bar of Pie Chart: Series1 Label & Value Issue | Charts and Charting in Excel | |||
| Dynamic chart pasted to a new workbook in report can't be dynamic | Charts and Charting in Excel | |||
| skipping blank cells in dynamic chart | Charts and Charting in Excel | |||
| Dynamic Ranges: Speed Issue | Excel Worksheet Functions | |||
Linear Mode
