Replace chart labels with different range to source data

[ollyhughes1982]

Hi. I have this ‘lollipop’ chart in my workbook, which is fed by the range C3:D29. However, I would like to the replace the data labels in the chart for those in the range H3:H29. is this possible? I obviously don’t want to just change the source data range, as it won’t work for the three countries at the bottom.

The reason I want to do this is that for the countries where parkrun no longer operates (Zimbabwe, Iceland and Russia), I want to show both the start and end date, rather than just the start date, as with all of the other countries.

I’ve tried the option to select label range, but it just doesn’t seem to work.

The file can be found here: https://1drv.ms/x/c/f08b781118912fd2/EYHODi_Rs5dFgU0wak2UF14BLU6rBoAtG6PSp9lETKCfLw?e=VyDC9o

Thanks in advance!

 

[myall_blues]

Is this what you wanted?

I deleted the existing labels and then added them again, and then replaced the default (which is Y value) with the range H3:H29. Note that the formula you were using to create the date TEXT was wrong - confusingly you need to use only three y's for a four digit year: =TEXT(A1,"mm/dd/yyy"), not =TEXT(A1,"mm/dd/yyyy").

Link to updated file

 

[ollyhughes1982]

Is this what you wanted?
View attachment 121197

I deleted the existing labels and then added them again, and then replaced the default (which is Y value) with the range H3:H29. Note that the formula you were using to create the date TEXT was wrong - confusingly you need to use only three y's for a four digit year: =TEXT(A1,"mm/dd/yyy"), not =TEXT(A1,"mm/dd/yyyy").

Link to updated file

Thanks, that’s almost it. I want the sates like that, but to also still have the country names as well

 

[RoryA]

I'd add those into the date cells:

=IFERROR(IF(C3="","",B3&CHAR(10)&IF(G3<>"",TEXT(C3,"mm/dd/yyyy")&" - "&TEXT(G3,"mm/dd/yyyy"),TEXT(C3,"mm/dd/yyyy"))),"")

 

[ollyhughes1982]

Brilliant. Thanks, both