Solved

Excel Formula: Alignment of data in list box need some TLC

Posted on 2011-02-10
18
322 Views
Last Modified: 2012-05-11
Experts:

I am using 2007, and I have a dashboard that has a list box that shows performance data.  I truncated all the info into one cell but this makes  the allignment out of wack.  Can some please show me the correct way of doing this?

I really would also like to have the down arrows red.

Thanks   NOC-Reports-r9.xlsm
0
Comment
Question by:Maliki Hassani
  • 10
  • 6
  • 2
18 Comments
 
LVL 41

Accepted Solution

by:
dlmille earned 250 total points
Comment Utility
At first blush, looks like you're being impacted by kerning based on the font.  You could use a font that doesn't have kerning to align everything, or use tab stops, if that's possible.  Otherwise, perhaps a control box that has multiple columns.

Let's take the easy route first.

See: http://www.mrexcel.com/forum/showthread.php?t=392333  for more on tab stops.  I'm taking a look now

Dave
0
 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 250 total points
Comment Utility
The alignment problem is easy. You can add tab characters instead of spaces. A formula like
=A60&CHAR(9)&TEXT(T60,"0,00%")&" "&IF(T60>0,"       ",IF(T60<0,"¿",""))
will work just fine. It will look bad in the cell, but in the listbox it will be just like you want.

Listboxes don't support more than one forecolor. You would have to write your own listbox class (which I doubt would be anywhere close to worth it). As far as I know, you can't even have different lines be different colors.
0
 

Author Comment

by:Maliki Hassani
Comment Utility
Tommy that works out!  

dlmille:  Think we will go with that one..  It's simple!  Thanks and great points!
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Here we go - 3 columns.  This is better...

Dave
NOC-Reports-r9.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Heck ... :)

Dave
0
 

Author Comment

by:Maliki Hassani
Comment Utility
Dave:

I am going to go with your idea now.  It looks a bit cleaner!
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Tell me more about how you created that downarrow...

Dave
0
 

Author Comment

by:Maliki Hassani
Comment Utility
The down arrow came from a previous question I posted today.  I was wondering the same thing..  lol
0
 

Author Comment

by:Maliki Hassani
Comment Utility
Dave:

I  am going to post a question on "Not enough system resources to display completely"
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
First - look - someone has asked that question.  see where they go with it...

http://www.experts-exchange.com/Q_26811532.html

Dave
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
I saw that error too. I though it was just because the computer I'm on is lame. I'll keep an eye on the question in case no one else answers it. Gotta go for today.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
@Tommy - I've been working with LANCE on a few of his questions and the error is pretty consistent.  It could be the volatile names he has if he hasn't deleted them.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Scratch that - the volatile names are gone
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
LANCE - Go ahead and ask the question as I've already got some new ideas to try

Dave
0
 

Author Comment

by:Maliki Hassani
Comment Utility
Sa-weet..  Yes, I did delete the names..  Asking question now then.  By the way it was a good post but not completely helpful.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
ok - refer to this question so we get flagged on it.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ok, I'm ready

Dave
0
 

Author Comment

by:Maliki Hassani
Comment Utility
Posted!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now