Tag Info

Hot answers tagged

8

Data validation will not change formatting of the cells. You will need to use Conditional Formatting to do what you desire. Once you have your lists in place, apply conditional formatting rules to those cells which use a formula that checks for the words RESTRICT and CASCADE. Select the cells with the lists Create a new conditional formatting rule Choose ...


3

You can do this with a single, fairly straightforward formula. For IP address in A1, use the following: =INDEX({"Class A","Class B","Class C"},MATCH(VALUE(LEFT(A1,FIND(".",A1)-1)),{1,128,192,224},1)) Breaking it down: VALUE(LEFT(A1,FIND(".",A1)-1)) uses string functions to identify the first part of the address and cast it as a number, not a string. ...


2

Rather than using Specific Text, try using Cell Value and equal to, then enter C for one rule and O for the other (do not enter equal or quotes when entering the letter, Excel does it for you). This will look for the only the letters that match. Applied to the whole column, this ignores text of the Open / Closed heading.


2

I'm not sure I understand exactly the effect you are after, but if you want to be able to copy (or drag to extend) a formula while keeping some reference the same, you can prefix either the column or row (or both) identifier with a $ sign. For example, if you have in cell A1 =B3 and copy this to cell A2, it becomes =B4 because the reference is updated to ...


1

This does what you want, if VBa is OK? So, you can see the start and end values. This is the range for each of the 3 classes (hence the A, B and C). Then you can see classA, classB and classC - I kept these incase you needed to change the value for any reason. Last is, what column does the IP live under (the columnToLookUp and lastly which column do you ...


1

One style of multi-row headings: Create a table with a single line header Select any of the heading cells, Hit f2 and then move the cursor to the end of the title/text Press and hold Alt, then hit Enter for all additional lines you wish, release Alt. Add any text you wish.


1

It turned out to be an update distributed through Microsoft Update. I removed all updates relating directly to Excel and Office Service Pack 2, and functionality was then restored.



Only top voted, non community-wiki answers of a minimum length are eligible