Make Your Microsoft Dynamics Investment Count & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.
Course Of The Month
7 days, 14 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How can I get the formatting of a field in one worksheet to transfer to subsequent worksheets within a work book.
Posted on 2016-08-10
I have a field containing text in my first worksheet and it contains some formatting. (e.g. the 3rd word of the sentence is bold, underlined and is in blue and the rest of the text is normal).
In my subsequent worksheets I have referenced this field by way of a formula: =Distributor!A22
However, the formatting of the referenced field is not coming across to the other worksheets.
Does anyone know how to write the formula so that it will present the field exactly as it is shown on the the referenced worksheet?
Thank you in advance!
In my subsequent worksheets I have referenced this field by way of a formula: =Distributor!A22
However, the formatting of the referenced field is not coming across to the other worksheets.
Does anyone know how to write the formula so that it will present the field exactly as it is shown on the the referenced worksheet?
Thank you in advance!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
6 Comments
Active today
You can't link formatting between cells, with a formula or otherwise. You also can't format the result of a formula as you require - that's only possible with constant values.
Active today
If the contents of the source cell aren't going to change then copy and paste the formatting to the subsequent sheets.
You can group the sheets and do the paste into all sheets in one go:
Copy the first cell, select the second sheet and select the cell where required and then press Shift and Select the last tab where required. Then Paste Special - Formats.
If the sheets requiring the format are not one after the other, use Ctrl + click to select each tab rather than Shift + click on first and last.
Then select the first sheet to ungroup the sheets.
You can group the sheets and do the paste into all sheets in one go:
Copy the first cell, select the second sheet and select the cell where required and then press Shift and Select the last tab where required. Then Paste Special - Formats.
If the sheets requiring the format are not one after the other, use Ctrl + click to select each tab rather than Shift + click on first and last.
Then select the first sheet to ungroup the sheets.
Thank you both. The value will change and so therefore the second comment will not work. I found a work around though. Thought i would share with everyone. It is slick!!!! (see below)
An alternative to the macro approach is to use the Camera tool in Excel. This has been covered in other issues ofExcelTips, but essentially the camera is a way to copy a dynamic image of a range of cells from one place to another. It is the image of the source cells that is shown, and it is shown as a graphic, not as the contents of any target cells. Since the graphic is dynamic, whenever the source cells are changed (including formatting), the image is also updated to reflect the change.
To use the Camera tool, you must customize your toolbar so that the tool is available; it is not available by default. When you are doing your customizing, the Camera tool is available on the Commands tab in the Tools section. It is near the bottom of the list of commands and looks—oddly enough—like a small camera.
With the Camera tool in place, follow these steps to use it:
1. Select the cells or range of which you want a picture taken.
2. Click on the Camera tool. The mouse pointer changes to a large plus sign.
3. Change to a different worksheet.
4. Click where you want the top left-hand corner of the picture to appear. The picture is inserted as a graphic on the worksheet.
An alternative to the macro approach is to use the Camera tool in Excel. This has been covered in other issues ofExcelTips, but essentially the camera is a way to copy a dynamic image of a range of cells from one place to another. It is the image of the source cells that is shown, and it is shown as a graphic, not as the contents of any target cells. Since the graphic is dynamic, whenever the source cells are changed (including formatting), the image is also updated to reflect the change.
To use the Camera tool, you must customize your toolbar so that the tool is available; it is not available by default. When you are doing your customizing, the Camera tool is available on the Commands tab in the Tools section. It is near the bottom of the list of commands and looks—oddly enough—like a small camera.
With the Camera tool in place, follow these steps to use it:
1. Select the cells or range of which you want a picture taken.
2. Click on the Camera tool. The mouse pointer changes to a large plus sign.
3. Change to a different worksheet.
4. Click where you want the top left-hand corner of the picture to appear. The picture is inserted as a graphic on the worksheet.
Active today
Yes, Camera tool would be an option.
The only downside that I could see with that is if the source cell changes size rather than any other formatting. The size of the image changes in line with the source cell but it does change the size of the cell where the image is overlaid.
Thanks
Rob
The only downside that I could see with that is if the source cell changes size rather than any other formatting. The size of the image changes in line with the source cell but it does change the size of the cell where the image is overlaid.
Thanks
Rob
Featured Post
It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Do you use a spreadsheet like Microsoft's Excel? Have you ever wanted to link out to a non excel file on your computer or network drive? This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data.
Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month7 days, 14 hours left to enroll
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.