Dear Sirs

I am trying to find out how to move an object or other indicator (a circle or a line marker etc) along a formatable horizontal line (so I can change the appearance) in an excel chart or spread sheet to indicate a set value against this line. The position of this object would be dependent upon a set value.

Eg. if the horizontal line represented zero to 20 (left to right) and the set value of the point was say 6.5 then the object would move to just less than half way along the line.Thanks

You could use the =REPT() function, it simply repeats a text string. So if you have the number 10 in cell A1 then in Cell B1 enter: =REPT("|",A1)

and you will get something like:||||||||||

Change the number in cell A1 and you change the length of the bar string.

MIKE

Check out this thread for a couple of options. You'll need to register (free) to download the files that show some examples. http://www.ozgrid.com/forum/showthr...

In addition, I tried something with Conditional Formatting that seems to work. It will need some finessing, but this might get you started:

1 - Insert a line through C10:V10 (20 Cells)

2 - Enter 1 through 20 in C11:V11

3 - Find a character in a Wing Ding type font that you want to use as your indicator. e.g. an R will produce a "spiked circle" in Wing Dings font.

4 - Fill C10:V10 with that character

5 - Select C10:V10 and apply a Conditional Formatting rule of =C11<>$A$1

6 - Set the Format to Font Color = WhiteWhe you enter a number in A1 that matches a number in D11:V11, your indicator will appear in Row 10 in the matching cell. All non-matching values will be formatted as White and not show up.

With 1 - 20 in C11:V11, you'll only be able to match whole numbers. If you need values such as the 6.5 that you posted, you can make the columns smaller and insert 1, 1.5, 2, 2.5, etc. in Row 11 and use as many columns as you need. The smaller your increments, the more columns you'll need.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

mmcconaghy and DerbyDad03 Thank you both for your proposals.

Regards

GedC

There is also a simple Conditional Formatting rule you could use. Try this:

If your data looks like this:

A 1) 100 2) 200 3) 300 4) 400 5) 500 6) 600 7) 700 8) 800 9) 900 10) 1000Conditional Formatting 2007

1) Select your cell or range of cells, IE A1 thru A10

2) On the ribbon clickConditional Formatting

3) Click onNew Rules, itâ€™s near the bottom of the dialog box.

4) ClickFormat All Cells Based on their value, should be top of list.

5) In theFormat StyleBox, selectData Bar

6) TheType Shortest Barbox should sayLowest Value, leave it alone.

7) TheType Longest Barbox should sayHighest Value, leave it alone.

8) In theBar Colorat the bottom, select a color.

9) Click OKYour list of numbers should now be shaded according to the number in the cell.

Changing a number, changes the length of the shading.

MIKE

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History