Formula affects row height?

[chejado]

[/img]

I currently have this formula in a range of cells down a column.

"{=SUBSTITUTE(MCONCAT(IF(A12=$O$12:$O$35,$Q$12:$Q$35," ")," "),"FALSE,","")}"

I have no problems with the formula (actually was able to put it together using this site) and it works as intended. Problem occurs when I autofit the row height of all affected cells. Affected meaning only those with the formula in them.
I have formatted the appropriate cells to wrap text and that works fine. I have tightened the column width to display the resulting values (text strings) on top of one another. I just can't get the cells to fit to the values. It looks as if there is an invisible text string above every result - to give you an idea of the extra space in the cell.
I have formatted all effected cells to every combination of text alignment and the results seem the same, though for some reason the very top record will remain flush sometimes (maybe that has to do with the text alignment combos I was trying) but every result below the top record keeps a blank space. I have a hidden column, but it doesnt have any formulas in the cells and the values in them are just numbers. Not sure if this affects it any but I have a database query that refreshes upon opening this worksheet and is where I grab the text for the formula to display in all affected cells (Q12-Q35). But none of the info in those cells have wrapped text because I have space to play with so I don't need to wrap it. Any help would be appreciated and if I could I would display the sheet but I see no attachment option. Thank you. [/quote]

 

[RalphA]

Have you tried highlighting the "affected" cells, then clicking on Format, Cells, Alignment, and then setting the Vertical to something other than Bottom?

 

[chejado]

Yes, I did try that. When I removed the formula I was able to autofit the cells to the data again. Put the formula back and once again, it wouldnt size to the cell. When I played with the text alignment I found one or two of the cells wouldnt go all the way to the bottom nor the top. But it wasnt consistent throughout the entire range of cells. Some would, some wouldn't. The only cell that would was the very top cell. It resizes perfectly (as it should). I did try something to see what it might look like, I copied all the data in the column and pasted to a word doc for sh!ts and giggles. It looked like this:

qqqqqqq
..qqqqqqq
...qqqqqqq
....qqqqqqq
.....qqqqqqq
......qqqqqqq

(the periods are for spacing only, this Message Body default to left indent for some reason and I've yet to figure it out)

OMG. I think I just figured it out. Its incrementing down the column because its comparing cells looking for a true statement when it matches. That explains why the first cell is aligned perfectly. When I indent left, it still doesnt help, that tells me that the formula takes precedent over the format of the cell?? Not sure, but on to something here. Maybe its my wackass formula. Needs fixed, but how??

 

[RalphA]

Examime your formula carefully. I see a couple of " ", that is, a double quote, a SPACE, and another double quote. Perhaps you should be using "", that is, double quote, double quote, with NO SPACE between quotes?

 

[chejado]

Thanks for the insight, I will try it first thing in the morning and respond. I see your in Texas, Im in Abilene. Until then...

 

[chejado]

Ralph,

Alright, played with quotes and found it did affect every cell. I was able to manipulate values to remain flush against the top of the cell. But, the downside to that was now there was no space between the multiple values listed. Which causes problems because some vary in length and I want them on top of one another, and its not like I can adjust the width to the proper size and move on. So, it looks as if I need a formula that MCONCATs a dynamic range. Might have to scrap the formula and look for something more tailored to my needs. Unfortunately, I don't have to knowledge base so if you have any ideas I would be much appreciative.

http://www.mrexcel.com/board2/viewtopic.php?t=130302&highlight=

This is the link that displays what I need and shows the problem on the sheet (one of my other posts that now I see run into another).

 

[chejado]

Ralph,

Not sure if you're still with me, but I'll respond for the sake of helping others. I found by using

=TRIM(SUBSTITUTE(MCONCAT(IF(A19=$O$12:$O$35,$Q$12:$Q$35,"")," "),"FALSE,",""))

I was able to acheive my desired results. Long formula, but it works.

When I replaced Substitute with only TRIM, I had no spaces between my results. It appears that SUBSTITUTE is a crutch for me right now because it offers the convenience of a space between value results.

I was able to produce flush values across the entire cell range (with the exception of one-just a matter of time before I figure that one out). Is there a more efficient formula, and since this was my first time every writing a formula there is bound to be a better way through VBA. But, it follows form, fit, function.

 

[RalphA]

Congratualtions! You did enough investigative work to discover a solution to your problem. Remenber that, "If it ain't broke, don't fix it" is a wise saying, also known as "Perfect is the enemy of good".

Regards,

Ralph