Custom Number

[sprog1977]

How do I create an entry in the custom number (of format cells) category to turn a #NA to a blank ("").

I can easily create this as a formula but I want it as a format.

Please help, this is really frustrating me!

 

[DaleWatson123321]

How about using Conditional Formatting. Here are the steps:

1) Place your cursor on the cell where you want to show blank instead of #N/A

2) From the menu: Format - Conditional Formatting

3) Change "Cell Value Is" to "Formula Is"

4) Use this type of formula: =ISNA(x) - where you change x to the cell you are on.

5) Choose "Format", and change the Font Color to White, and then OK twice to exit.

If you need this formatting in other cells, you can Copy and use Paste Special "Formats".

Hope this helps.

Regards, Dale Watson

 

[sprog1977]

The formatting is for data labels within a line chart. Therefore unable to access conditional formatting. Have you any other ideas?

Thanks for the suggestion.

 

[xlbo]

Where are your #N/As being populated from - seems to me that you would be better off fixing this at the source rather than hiding it at the end


Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[DaleWatson123321]

In your type of situation, my solution would be to use VBA to extract ONLY the data that meets your criteria - i.e. data that does NOT show #N/A.

The VBA code would be activated by a click-of-a-button, and would extract the data and update the chart - all at the same time.

If you'd like to use this option, let me know and I'll post the VBA code.

Regards, Dale Watson

 

[KenWright]

You could create another range of values using formulas such as =IF(ISNA(rng),"",rng) etc and then use Rob Bovey's XY Chart Labeler to add the labels from that range to your chart. Works on most chart types and not just XY, despite the title:-

http://www.bmsltd.ie/mvp/

If you don't want the extra range then ask Dale to give you some code and go with that, but personally I'm with Geoff in that this issue should always be fixed at source, and not just fix the symptoms.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[sprog1977]

Agreed with fixing things at source but this is not my spreadsheet.

Dale could I please have this code. Could be very useful.
Thanks.

 

[DaleWatson123321]

sprog,

I'll post the code later (today) - once I've had a chance to prepare it - i.e. with some documentation and steps.

But first, because you mention it's not your spreadsheet, will you be able to add the code to the spreadsheet? Or, will you be setting up a separate file - into which you'll be copying and pasting the data that you'll use to populate your chart? And if so, do you also want code for copying and pasting the data?

Regards, Dale Watson

 

[sprog1977]

I have access to the file so I won't need to create a seperate file.
The code for copying and pasting would be great.

Thank you so much for your help!!!

 

[DaleWatson123321]

sprog,

Well here goes... There's a lot of information for you to deal with. I hope that with my documentation (much of it within the VBA code) you'll be able to understand what needs to be done in order to set this up in your file. Naturally if you encounter any difficulty, get back to me and I'll provide further clarification.

Setting up your data:
The first requirement is that you set up field names for all the columns of data in your "database" - i.e. the data from which you'll be selectively extracting data for your chart.

The field names need to be WITHOUT spaces in the name. You might want to insert a separate row (which you can hide afterwards). You could use names that relate to the data contained in each column, or you could simply use, for example, fld_1, fld_2, fld_3, etc.

After you've entered the field names, you need to create a range name for your data. In my VBA code, I use the name "data". You should use the same name (at least for now).

To create the range name "data", use these Steps:

1) Place your cursor on the row containing the field names, and on the left-most field name - e.g. Column A if your first field name is in Column A.

2) Holding down the <Shift> key, highlight all the current data - i.e. down to include all rows containing data, and across to include all the columns for which you've entered field names.

3) Hold down <Ctrl> and hit <F3> - opens the "Define Name" window.

4) Type the name: data ...and hit <Enter>

NOTE: You'll only need to create this name ONCE - because the VBA code includes a routine to adjust the "data" range for any records added (or deleted).

The following code needs to be copied into a Module. Hold down <Alt> and hit <F11>, and then from the menu, choose: Insert – Module.

Sub Update_Chart()
    'This is the main routine - from which all other
    '(sub)routines are called.  To activate this code,
    'you could add a button to your worksheet,
    'right-click on the button, choose "Assign Macro"
    'and then choose this main routine (Update_Chart).
    
    'Naturally, don't expect it to work until AFTER
    'you've performed all the steps covered below...
    
    Application.ScreenUpdating = False
    Update_DataRange  'see routine below
    Extract_Data      'see routine below
    Set_Extdata       'see routine below
    Set_Chart         'see routine below
    
    'You'll need to change the filename below
    '(currently Chart Example.xls) to the same
    'name as the name of your file.
    Windows("Chart Example.xls").Activate
    
    ActiveCell.Select
    Application.ScreenUpdating = True
End Sub

Sub Update_DataRange()
    'Updates the range name "data" - for the
    'number of records in the sheet containing
    'your data (from which chart data is derived).
    
    'You'll need to change "Database" below to the
    'name of the sheet containing the data.
    Worksheets("Database").Select
    
    [data].Select
    ActiveCell.Select
    topcell = ActiveCell.Address
    
    'You need to change the row-offset below
    '(currently 10), for the number of columns in
    'your database.
    botmcell = [A65536].End(xlUp).Offset(0, 10).Address
    'Column A above is a column in my data that
    'will ALWAYS be populated.  You'll possibly
    'need to change to another column - if your data
    'won't always contain data in Column A.
    'If were to change the Column from A to C for
    'example, then you would also need to change
    'the offset (currently 10) to 8. (10 less 2 = 8
    'because the starting point (Column C) is
    '2 columns to the right).
    
    rng = topcell & " : " & botmcell
    'Based on 'topcell' & 'botmcell' above, the
    'following row resets the range name "data".
    Range(rng).Name = "data"
End Sub

Sub Extract_Data()
    'This is for extracting "selective" data
    ' (i.e. without the #N/A rows) to a separate sheet.
    'Note:  A "copy" of the data is extracted –
    'leaving the original data "as is".  You'll need to
    'insert a sheet for this purpose, and give the sheet
    'a name.  I'm using the name "ChartData" (the next
    'subroutine (Set_Extdata) references "ChartData").
    
    'The code below references 3 range names:
    
    '1) "data" - is the range name already mentioned –
    'in the Update_DataRange routine (above).
    
    '2) "crit" - is a range name that you'll need to assign
    'to 2 cells on a worksheet.  Preferably, you should
    'create a separate sheet for criteria used with Excel's
    'Advanced Filter - because this will avoid problems
    'that can arise, especially in earlier versions of Excel.
    'I always name this separate sheet "Criteria".  On this
    'sheet, I usually also include notes relating to the
    'criteria and misc. other aspects of the file that relate
    'to automation, etc.
    
    'On the Criteria sheet, I normally enter the criteria
    'starting in Column B, because I reserve Column A
    'for entering (as a label) the name of the range name
    'I assign to each criteria I create.
    
    'For now, you only require the one criteria, named
    '"crit".  Therefore, I would enter the label "crit" in
    'Column A (any row near the top is fine - doesn't
    'have to be row 1).  To the right of the cell you enter
    'the label "crit", assign the range name "crit".  As
    'previously mentioned, this name has to be assigned
    'to 2 cells.
    
    'Use these Steps:
    '1) Highlight the cell opposite the label "crit", and the
    'cell immediately below - in Column B. (e.g. if you
    'entered the label "crit" in A5, then highlight B5:B6)
    '2) Hold down <Ctrl> and hit <F3> (This opens the
    '"Define Name" window).
    '3) Type the name - e.g.:  crit  ...and <Enter>.
    
    'The following formula needs to be entered in the
    '2nd (lower) cell of the range named "crit" - B6 in
    'the above example.
    '=XXX<>"#N/A"   ...where you need to replace the
    '"XXX" with the field name that identifies the column
    'where you'll have "#N/A" for some of your records.

    'IMPORTANT:  Leave the top cell (B5) empty.
    'This is REQUIRED when a formula is used in
    'creating criteria.
    
    'NOTE:  The above example is the simplest of
    'examples (2 cells).  Whenever required, it's possible
    'to use any number of cells - other columns to reference
    'additional fields, and/or other rows to include
    'additional criteria.  Basically, including additional
    'fields makes it a "AND" condition, and including more
    'rows makes it an "OR" condition for the criteria.
    
    'Back to the range names below - now the 3rd name
    '- "out".  You need to assign this name on the
    '"ChartData" sheet (mentioned previously), to the cells
    'containing the field names of those fields containing
    'the data you wish to extract for the chart.
    
    Range("data").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:="crit", _
    CopyToRange:=Range("out"), _
    Unique:=False
End Sub

Sub Set_Extdata()
    'This routine assigns the range name "extdata" to the
    'data extracted to the "ChartData" sheet.
    Worksheets("ChartData").Select
    [out].Select
    ActiveCell.Offset(1, 0).Activate
    topcell = ActiveCell.Address
    
    'The row below references Column "A", with column
    'offset of "1", to reflect two fields of data being
    'extracted.  Naturally if you are including a different
    'number of fields, you'll need to change this row
    'accordingly.
    botmcell = [A65536].End(xlUp).Offset(0, 1).Address
    
    rng = topcell & " : " & botmcell
    Range(rng).Name = "extdata"
End Sub
    
Sub Set_Chart()
    'This routine simply updates the existing chart with
    'the data extracted - i.e. the data named "extdata".
    'Be sure, however to change the Chart # below
    ' see ChartObjects(“Chart 1”) to match 
    'with the Chart # of your existing chart.
    Worksheets("Chart").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SetSourceData [extdata]
End Sub

I hope the above proves to be what you require, and not too difficult. But as mentioned, let me know if you need help.

Regards, Dale Watson