Chart problem

Topic

New Reply

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?

Thanks in advance.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

In that case, you should change the lookup values to numbers too.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

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.

Reply | Quote