Better and more powerful communication is always a worthy goal. This is, of course, just as true in Excel as in other business venues. One way to do this is to Mix Text with Formulas.
By
using the Concatenation (simply done with Ampersands) feature along
with Formulas,
you can build interactive messages within your reports, making them easier to
understand and less likely to incur human error.
Using
the example illustrated below (click on it to enlarge it), let’s say that you have a table of data and a
formula that returns the sales for the 4th quarter in cell G6.
The formula in G6 interacts with the dropdown box in cell C4, (dropdowns are a snap to create
using a Validation list…), to display the 4th Quarter Sales by Rep.
You
could stop there and hope that the users of the report understand what is being
shown, or you could create a piece of Excel Magic in cell F6.
As
you can see in the formula bar, a Text string is used in
conjunction with the name being shown in the dropdown box in C4:
=“4th Quarter Sales
for ”&C4&“:”
The outcome is an Interactive Message that works with the dropdown box and the results of the formula used in cell G4. Note: Leave a space after the end of the text string so as to mimic a proper sentence.
As
illustrated, try using ampersands with text strings combined with formulas in
your next report. Your users will thank you!
No comments:
Post a Comment