$‘Monthly Summary’.$A$1:$A$5;$‘Monthly Summary’.$B$1:$BZ$5
What is wrong with the Calc Data Range syntax above? I am trying to use A1:A5 column as first column label, and data range B1:BZ5 for chart data
Hi Villeroy
Thanks for your prompt answer. Unfortunately, there are about 200 columns between A and B columns and I’ want the A1 column values (blood pressure categories) retained as Legend labels and my data range to start from B1 to BZ5. The syntax I tried - as per Calc Help article - is rejected (stays red and I can’t OK it). If I just define data range as B1:BZ5 then my B1 column data (blood pressure reading values) become Legend labels instead. Is there a way to have A1:A5 kept as Legend label and data range to be B1:BZ5?
Thanks again for your help - as you can see I am really not au fait with Libre Calc.
Please upload your ODF type sample file here.
2 Likes
Did you open the attached file t90270.ods? It demonstrates that A1:BZ5 with column labels in A1:A5 works as expected.
Yes, I did. But, I need to skip about 200 columns between A1 and B1 and yet retain A1:A5 texts as labels. If I don’t skip A2 to B1 there will be extra 200 columns in my chart and I can’t handle/print such huge chart.
Select the range of cells you want to skip and call menu:Format>Columns>Hide
Something like
$Sheet1.$A$1:$A$5;$Sheet1.$C$1:$D$5
i.e. two separate ranges, as data range works perfectly fine. Choose Data series in columns and First column as label.
You can also select the ranges before invoking the Chart dialog, or if the chart exists already select the ranges and drag&drop them onto the chart.
1 Like
This is exactly the same as my original syntax question:
$‘Monthly Summary’.$A$1:$A$5;$‘Monthly Summary’.$B$1:$BZ$5
which stayed red and wouldn’t let me OK the range change. Maybe that’s due to the fact that I hadn’t hidden the A2:AWZX columns? Certainly, your range syntax looks exactly as mine which wouldn’t pass.
Anyway, thanks very much for your help, particularly as today is Easter and I’m troubling you with this stuff. I’ll try some of your suggestions to see if I can overcome this issue.
Thanks. I’ll check the ; as that is the only character I introduced in the syntax that has detached range - the apostrophe was copy-pasted from the original syntax
$‘Monthly Summary’.$A$1:$AZ$5
so the faulty syntax
$‘Monthly Summary’.$A$1:$A$5;$‘Monthly Summary’.$B$1:$BZ$5
has just the ; introduced (and range changed).
Will let you know if the character value of ; was the issue. Thanks again for your advice.
No luck. I think I used the right character for ; but could not change the range. I am appending the screenshot of the failed range change - maybe you can spot something odd there.
I don’t want to bore you to death with this. Just a brief explanation:
I am 73 y.o. and have an inoperable hearth condition which requires me to maintain twice daily (am and pm) blood pressure monitoring. I’ve been doing this since my diagnosis in Nov 2017. I summarise twice daily readings into another sheet in Weekly Summaries and into a third sheet Monthly Summaries. I am trying to produce Monthly and Yearly charts to enable me to send to my cardiologist these monthly and yearly charts so that he can quickly adjust medications and doses (I take 7 different medications right now). BTW, there are 3 readings - systolic blood pressure, diastolic BP and pulse. These 3 measurements are then converted into 3 additional interdependent data that mean something to the cardiologist. My Daily data sheet has all the 3 readings and the 3 additional data categories for the am and for the pm readings. So, there are 6 rows of data in the am section and 6 in the pm section for each day, in about 2000 columns (cca the number of days since I started these measurements). The last time I used Excel in anger was about 30 years ago when I was a modestly competent amateur user. I now use LibreOffice as I do not have any MS licences any longer. Hence my ignorance about LibreOffice Calc.
Sorry about this lengthy explanation and once again thanks for your help, even if I did not succeed in resolving this. I’ll think of alternative strategies - maybe I’ll create monthly sheets instead of consolidating 2000 columns into Yearly summaries.
It may be that your function separator is , comma instead of ; semicolon, in that case you’ll have to use comma to separate the ranges as well.
1 Like
EUREKA, it was , and now it works fine.
Thanks a lot, a lot, a lot.
I’ll see how I can acknowledge your HELP & SOLUTION on this site.
Enjoy what’s left of the Easter break.
If I set the comma as function separator, and type semicolon, Calc changes it to comma. Why not in this case? Thanks.
1 Like
I don’t know why Calc doesn’t automatically change the ; to , . I doubt this is due to the fact that I’m using Chart Wizard. As far as LibreOffice version I am running 7.5.1.2 (X86_64) - see attached screenshot.
Anyway, your trick worked perfectly and I thank you again for your patience and help.
Best regards DesperateDan (Slobodan Milosevic)
For future reference, clicking the icon on the LibreOffice About dialog will copy the version information to the clipboard, saving you the need to screen capture.
1 Like