Indent in relation to numeric cell value

[jason.hampton]

All,

Attached is a sample spreadsheet created in Excel 2010.

Column A is an excerpt from a large workbook I am working on. My goal is to have the data in Column A indent based on its numerical value.

I was eperimenting and created an IF arguement in Column B; which somewhat accomplishes this. My concen is that if you hid column A, the formula in column B could be altered by accident.

I've tried to create a conditional formatting for this; to no avail. Any help would be appreciated!

[Sam Capricci]

Is there some reason you don't just want to highlight col B and use the alignment button in the Home tab?

[jason.hampton]

Sambo kid,

I don't want to indent it manually. Besides there are times when the numeric values are as high as 14. My goal would be to have the condition result in - cell value =0; indent 0; cell value =1; indent 1 etc.

[Sam Capricci]

Ok, just wanted to check that. Sometimes the most obvious answer is overlooked.
So just to be a little more clear, 0 isn't indented, 1 is bumped in one space, 2 - 2 spaces, 3 - 3 spaces etc? Is that correct?

[jason.hampton]

Yes sir, that is correct

[Sam Capricci]

That'll help whoever is able to solve this one. If you don't want to use a formula (because my guess would have been something along the lines of where you went or a substitute formula), I tried a find and replace but haven't gotten that to work yet.

[ARGK]

Hi
I found a function on the internet that did what you want. See sample attached.
Once you have the formatting you want you can remove the formulae.
Good luck.
Tony

Note: The function is stored in a module and called from a formula in each cell in Column C in the sample.

[jason.hampton]

ARGK,

Thank you for that! It seems to work flawlessly. One benifit is this code will work on non-.xslm files. There would not be any need to enable macros.

Attached is a macro code one other guy in my office created me, in tandem with this posting.

Please Login or Register  to view this content.

[martindwilson]

why not just use in col b
=REPT(" ",A2)&A2
unlock all cells
then hide col a
then lock cells and hide formulas in column b only
then protect sheet

[jason.hampton]

martindwilson,

That seems to work very well. Depending on what you are doing you could modify the protection of the worksheet to select, modify cells when protected. Thanks.

[martindwilson]

you just unprotect when you need,there are lots of options in protect must admit i havent tried them all but you only have to lock column b