Hi there guys ,
I'm struggling to find a solution for this in the form of a macro: I want to display values that are entered into cells from different sheets in a spreadsheet to be displayed in bold; for instance:
="The employee of the month is "&Sheet1.A1&". He works in the "&Sheet2.A1&" department."
So that it looks like this: The employee of the month is Peter. He works in the Admin department.
Is there any way achieve this? Any help would be greatly appreciated
[Solved] Having fetched cell values in a row display in bold
-
- Posts: 6
- Joined: Tue Sep 02, 2014 11:43 am
[Solved] Having fetched cell values in a row display in bold
Last edited by MrProgrammer on Tue Nov 17, 2020 1:58 am, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Reason: Tagged ✓ [Solved]
OpenOffice 3.1 on Windows Vista
Re: Having fetched cell values in a row display in bold
Do you want to format a part of the result string by macro?
It is not a simple task... if at all, this can be achieved...
It is not a simple task... if at all, this can be achieved...
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
-
- Posts: 6
- Joined: Tue Sep 02, 2014 11:43 am
Re: Having fetched cell values in a row display in bold
Yeah ,that's precisely what I want it to do . I want only whatever was entered into Sheet.A1 and Sheet2.A1 to be displayed in bold in the line. I've done some digging around and I have only found examples where people already have set values that should be in bold so those macros look for those values and change them to bold , but in my case the values will constantly change according to whatever is entered in those 2 cells. I unfortunately not proficient enough at coding to make adjustments to their macros to work for what I want to achieve here :/
OpenOffice 3.1 on Windows Vista
Re: Having fetched cell values in a row display in bold
A workaround tip:
Use 5 adjacent cells for this task. In this case you can format the cells ifferently.
| The employee of the month is | Peter |. He works in the | Admin | department. |
Use 5 adjacent cells for this task. In this case you can format the cells ifferently.
| The employee of the month is | Peter |. He works in the | Admin | department. |
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
-
- Posts: 6
- Joined: Tue Sep 02, 2014 11:43 am
Re: Having fetched cell values in a row display in bold
yeah, I'm doing it like that at the moment but depending on the length of the string entered in the cells it refers to the result sometimes looks horrible because of the open spaces between the bold stylized cell and the "normal" text that follows it
OpenOffice 3.1 on Windows Vista
- MrProgrammer
- Moderator
- Posts: 4895
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Having fetched cell values in a row display in bold
I know of no practical way to achieve that in a spreadsheet cell. A cell has its formatting set by a style and that style applies to every character in the cell. However, you can use Open Office to create a poster for the employee.permethium wrote:="The employee of the month is "&Sheet1.A1&". He works in the "&Sheet2.A1&" department."
So that it looks like this: The employee of the month is Peter. He works in the Admin department.
Is there any way achieve this?
• Create a new sheet "Both" with the name and department in the same row. Save the spreadsheet. *
• Create a new database using the spreadsheet as its data source.
• Create a new text document containing: The employee of the month is . He works in the department.
• You'll be inserting two fields next: Insert → Fields → Other → Mail merge fields → {YourDatabase} → Both → {YourColumn}.
• Before the first "." in the text document insert a mail merge field to receive the name.
• Before the word "department" insert a mail merge field to receive the department.
• Make the fields bold, say with Format → Styles and Formatting → Character styles → Strong emphasis.
• Save the document as a template; close it.
• Open the template and use View → Data sources.
• Locate your database and the Both table in the data sources.
• Select the record and use Data to Fields in the toolbar.
Read about Mail Merge and using a spreadsheet as a database's data source in Help → Index or in User Guides (PDF) or searching for topics about them in the Calc Forum or Writer Forum. This may seem like a lot of work, but once the template is created it will take just a few seconds to select the record from the database and pull it into the text document.
* You may be able to do this without creating a new sheet and select the records from the existing sheets. I haven't worked with that configuation myself.
If you want to use a spreadsheet use characters, not formatting, to highlight the words.
="The employee of the month is ••"&Sheet1.A1&"••. He works in the ••"&Sheet2.A1&"•• department."
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
-
- Posts: 6
- Joined: Tue Sep 02, 2014 11:43 am
Re: Having fetched cell values in a row display in bold
Seems like we ran into a rather fundamental shortcoming of calc . Thanks much for the suggestions and workarounds guys - very much appreciated indeed! , will be experimenting today and let you know what popped
OpenOffice 3.1 on Windows Vista
Re: Having fetched cell values in a row display in bold
I don't know much about Calc but I presume you can use a listener for cell change events and search through the cell to find what you want to bold. Perhaps this page may help
viewtopic.php?f=45&t=80498
There's a bold style function here:
viewtopic.php?f=9&t=56601
If you can't bold what's changing in your cell then one idea might be to set things up the reverse way - set the style for the whole cell as bold - but then change the character style of everything else - the bits that aren't changing - to normal. But like I say I don't know Calc.
viewtopic.php?f=45&t=80498
There's a bold style function here:
viewtopic.php?f=9&t=56601
If you can't bold what's changing in your cell then one idea might be to set things up the reverse way - set the style for the whole cell as bold - but then change the character style of everything else - the bits that aren't changing - to normal. But like I say I don't know Calc.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)