Results 1 to 7 of 7

Thread: Chart problem

  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am having a problem with a chart that updates automatically (since the source data has changed from 1,2,3....... to 1st,2nd,3rd)

    Please see the attached mock file. If you copy the data from U24:U40 to V1:V17, and then select the chart tab, you will see that everybody moves to 1st position. What do i need to change?

    [attachment=86181:Chart_problem.xls]

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The values in M1:U15 contain numbers that have been formatted with a custom number format to be displayed as 1st, 2nd etc.
    The values in U24:U40 are text values. You should use numbers here too, with custom number formats. Otherwise you won't be able to use them in the chart.

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I soooooooo wish I had not started this, but this is my last problem with the conversion.

    The values in U20:U40 are formula's (not in the mock that I posted) > =VLOOKUP($A1,$A$19:$V$190,22,0), the lookup values being text also. Would it be possible to return the value from the text in the formula? At which point, i can run the macro that you kindly provided.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In that case, you should change the lookup values to numbers too.

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I can't do that, it goes back too far. I'm in too deep

    Unless there is a macro I can run to convert the text back to values?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In the topic Custom format for rankings you asked for formulas to convert numbers 1, 2, ... to text strings 1st, 2nd, ...
    In that topic, you were also shown how you could avoid this. So you should apply that. It makes no sense to first convert numbers to text, then convert them back to the original numbers. It's inefficient and unnecessary.

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='799875' date='25-Oct-2009 23:05']In the topic Custom format for rankings you asked for formulas to convert numbers 1, 2, ... to text strings 1st, 2nd, ...
    In that topic, you were also shown how you could avoid this. So you should apply that. It makes no sense to first convert numbers to text, then convert them back to the original numbers. It's inefficient and unnecessary.[/quote]

    Ok, fair point, I took the wrong road with the text strings, and need to revert to numbers. A fair bit of work, but a lesson learned. I have a few problems, but I will continue in the other thread, as the questions seem more appropriate there.

    Thanks for the good advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •