I am going to try my best to explain what kind of help I need but I will be happy to clarify:

I have a spreadsheet with two sections. One section is a list of states and the number of teams we need for a certain type of service (i.e. Montana 2 Tennessee 3). It is two columns of states and two columns of numbers with hidden calculations.

On the same sheet I have another section where our CSR can type notes on certain team members or type notes on the needs of customers in states where there are not enough team members (i.e. they can write a sentence or a paragraph in the cell and the cell will expand to show everything they write).

My question:
Is there a way to format the cells that have the state names and the number of teams to not expand if the CSR writes a paragraph?

Right now every cell in the row will expand if the CSR writes more than a sentence in just one column. I dont want the cells that have state names and team member numbers to expand along with the notes cell.

I hope you guys can understand this and someone has a solution.

Thanks for the help!

[Edited on July 8, 2008 at 9:31 PM. Reason : Forgot to say thanks]

7/8/2008 9:30:10 PM

if you format the column with the notes in it - go to Format > Cells > Alignment and un-check "Wrap text". That should force all the sentences to stay on the same row and not wrap. It will either run off the right edge of the screen or be hidden by other cells.

if that is not what you're looking for, take a screenshot or something and upload it to your photo gallery so we can see what the problem is

7/8/2008 10:16:27 PM

another option is to upload your spreadsheet somewhere and let us take a look at it.

^if you dont have word wrap checked, how can you force the text that runs over to be hid by the next cell to the right (instead of continuing on over the next cell(s))

7/8/2008 10:19:52 PM

^there's probably a prettier way of doing that, but i'd just type a space in the next column over

7/8/2008 10:27:29 PM

yeah, i was gonna say - it's hackish, but if you just put a space in the next column over, the long cell won't overflow

7/8/2008 10:29:52 PM

oh ok, i was thinking there might be [should be] a more 'elegant' way to do it

7/8/2008 10:43:31 PM

Quote :

"if you format the column with the notes in it - go to Format > Cells > Alignment and un-check "Wrap text". That should force all the sentences to stay on the same row and not wrap. It will either run off the right edge of the screen or be hidden by other cells."

I wish it was that easy, but the CSR wants to be able to see all of the notes that he types, along with the managers and accountants. They dont want to take the trouble of clicking on the cell to read all of the notes. But they also dont want the huge spaces on the state listings that form when the notes are paragraphs in length.

I will take a screenshot tomorrow. I may need to edit out some information first (not sure how much info can be public).

7/8/2008 10:54:48 PM

right, that's kind of what I figured at first, but it sounds like you're asking for too many things
on the one hand, you want the states to be lined up on top of each other with no big ugly spaces in between
on the other hand, you want cells in the same row as the states to be big enough to read several sentences in

something has to give

7/8/2008 11:00:21 PM

yeah i know Im asking for both and Im not really expecting a solution but I dont think Im the first person to want to figure out a way to do it.

Ive been playing with it for about a week now off and on and I havent figured out a way, but I want to make sure there really is no way before I tell the guys signing my paycheck that its not possible.

7/8/2008 11:09:37 PM

it's excel. it's a spreadsheet ... if they want a custom software solution, they need to pay someone to develop it.

7/9/2008 2:07:11 AM

went ahead and told them I cant do it. Thanks for the help guys.

7/9/2008 4:27:42 PM

anytime.


i'll always be here for you, if ever you want something shot down

7/9/2008 6:43:34 PM

new question...

i'm working with a table of about 350 rows... we'll call one of the columns "ranking" in which there will be a number b/t 1 and 350...

if i change the ranking of one the items (say i want to change the item ranked #50 to #2), i want to "re-sort" the data after increasing the ranking of all the subsequent items... (so #50 moves to #2, the old #2 goes to #3, the old #3 to #4, etc... until the old #49 becomes the new #50, #51-350 remain unchanged)

i know a macro will be required... i'm just not sure how to go about increasing the ranking number before the sort

[Edited on August 13, 2008 at 4:00 PM. Reason : grammar]

8/13/2008 3:58:03 PM

I'm pretty sure you can do this without a macro. Agentlion is in Colorado at the moment or else this would already be answered.

8/13/2008 4:15:39 PM

are all the rankings manually inputted or are the rankings based off of some criteria already on the spreadsheet? =RANK(blahblah) will do the dynamic updating: if the old #50 had its criterium changed such that it is now #2, all the ones between 2 and 50 will update accordingly.

8/13/2008 5:39:01 PM

no... the ranking is completely arbitrary and there's obviously no formula in the cells... i need for another user to be able to change a ranking of an item as they please - and then possibly click a button attached to a macro to re-rank those that need to be moved down, and then sort them

[Edited on August 14, 2008 at 8:16 AM. Reason : .]

8/14/2008 8:13:13 AM

^ i finally got around to working on that problem, and I think I have a solution in a Macro.

I have a sample worksheet here
http://joelion.com/temp/Rank.xls

You can change adjust any of the ranks in Column A, then hit the "Rank" button, and everything will be re-ranked and re-sorted (as a bonus, using Conditional Formatting, when you change a rank, that cell will turn red to indicate that a change has been made and it has not been re-ranked/sorted).
Go to the Macro Editor if you want to grab the code and integrate it into your own workbook. Also, just the VBA code is here
http://joelion.com/temp/Rank.txt

Do you know how to add a button and link it to the code? Go to View > Toolbars > Control Toolbox. Then select the "Command Button" option and draw a button on. Right click the button and go to Properties, and there you can adjust the text (caption) and name of the button. Then if you just double-click the button while in edit-mode, it will open up the VBA editor, and you can drop the code inside the subroutine that it generates for you.

The macro should work pretty well without many additional changes. I've tested all the main border cases, and it seems to handle them all. Two things you might need to change:

    rowOffset = 1   ' Number of rows before the ranks start.
    rankColumn = 1  ' the column where the ranks are stored. A=1, B=2, etc

9/9/2008 1:30:06 PM

one more thing that should be noted - you can only change one rank at a time!! After you change one rank, you have to hit the button. Then you can change another rank. But if you change multiple ranks at once, the re-ranking will not work.

9/9/2008 2:09:35 PM