You can refer to other sheets in your Conditional formatting in earlier versions.
Just name the range you're going to refer to then use the name in the Conditional Formatting formula.
The same applies to Validation. You can use a list on another sheet as long as you name it and refer to the name in the validation settings.
Discussion on:
View:
Show:
Can you offer some specific step instructions? I tried that this morning and couldn't get it to work -- still got the "can't reference another sheet" error using Southwest!D2. Using just Southwest didn't return an error, but didn't work either -- nothing happened.
Give the cell Southwest!D2 a name using "Formula tab/Defined Names/Define Name". "rngSW" for example.
When you're entering your conditional formatting statement, rather than entering "Southwest!D2", enter "rngSW". In other words, make sure any reference to another sheet refers to a range name rather than a range or cell address.
Regards
When you're entering your conditional formatting statement, rather than entering "Southwest!D2", enter "rngSW". In other words, make sure any reference to another sheet refers to a range name rather than a range or cell address.
Regards
I've been doing validation that way since I discovered validation. Just didn't know you could do conditional like that. I'll give it a try.
My newest favorite in Excel is vlookup. I didn't realize you could do that in 2003. I have Office 2007 on my laptop and at home, but STILL haven't been upgraded to 2007 at work, so I've been using my laptop to do my statistics. At least I have 2003 - one of the others in my department still has office 2000!
My newest favorite in Excel is vlookup. I didn't realize you could do that in 2003. I have Office 2007 on my laptop and at home, but STILL haven't been upgraded to 2007 at work, so I've been using my laptop to do my statistics. At least I have 2003 - one of the others in my department still has office 2000!
... can be used in Validation.
Assume you have an input sheet where the user selects a Staff Type, then a Pension Plan. The difficulty is that the Plans available depend on the Staff Type.
Set up a table like this with the staff types across the top and the available plans listed under each one.
Boss Manager Junior
Plan A Plan A Plan C
Plan B Plan C Plan D
Plan E
The boss has a choice of three, the Manager and Junior have only two. Name the body of the table(ie all except the top row) "planList" and the top row as "typeList".
To validate the Staff Type cell, lets make it A1 for example, select a List type validation and enter "=typeList" as the source. So far pretty standard, note lists can be horizontal or vertical.
To validate the Pension Plan cell, lets make it A2, choose a List type validation and enter the following formula.
=OFFSET(planList,0,MATCH(A1,typeList,0)-1,ROWS(planList),1)
This says, find the column my selection in cell A1 is within the typeList using the Match function, and subtract one (because Matchs start at 1 and Offsets start at 0) to give the column of the planList table to use.
The Offset function says, give me the part if the planList that is in the appropriate column, and make it one cell wide and as tall as the lookup table might be(ROWS()).
The formula may seem a bit complex if you haven't worked with match and offset before, but a bit of experimentation will get you there.
You can extend this to count the number of rows in the relevant column, so you only see the real values. Hint, use count and counta instead of the Rows() construct.
This is really just scrathing the surface of what is possible in a validation formulae. If you are familiar with "array-formulae" they will also work in the validation list anabling you to sort a list for display and all sorts of other options.
These methods give you enormous power in providing just the right options to your users and allowing you to easily change options without needing to recode all your validation lists by hand. You can use information entered by the user to validate other inputs. You might have a list of people and an input cell for each where you can select their spouse. Use the whole list as the source for the Spouse cell. As the users adds people, the validation lists grows accordingly.
The last bit of the puzzle is that you can even create your lookup formulae as "Named Ranges" and then just refer to the name in the Validation List area. So, only one copy to change to update all similar input cells making the process much faster and easier to manage.
Assume you have an input sheet where the user selects a Staff Type, then a Pension Plan. The difficulty is that the Plans available depend on the Staff Type.
Set up a table like this with the staff types across the top and the available plans listed under each one.
Boss Manager Junior
Plan A Plan A Plan C
Plan B Plan C Plan D
Plan E
The boss has a choice of three, the Manager and Junior have only two. Name the body of the table(ie all except the top row) "planList" and the top row as "typeList".
To validate the Staff Type cell, lets make it A1 for example, select a List type validation and enter "=typeList" as the source. So far pretty standard, note lists can be horizontal or vertical.
To validate the Pension Plan cell, lets make it A2, choose a List type validation and enter the following formula.
=OFFSET(planList,0,MATCH(A1,typeList,0)-1,ROWS(planList),1)
This says, find the column my selection in cell A1 is within the typeList using the Match function, and subtract one (because Matchs start at 1 and Offsets start at 0) to give the column of the planList table to use.
The Offset function says, give me the part if the planList that is in the appropriate column, and make it one cell wide and as tall as the lookup table might be(ROWS()).
The formula may seem a bit complex if you haven't worked with match and offset before, but a bit of experimentation will get you there.
You can extend this to count the number of rows in the relevant column, so you only see the real values. Hint, use count and counta instead of the Rows() construct.
This is really just scrathing the surface of what is possible in a validation formulae. If you are familiar with "array-formulae" they will also work in the validation list anabling you to sort a list for display and all sorts of other options.
These methods give you enormous power in providing just the right options to your users and allowing you to easily change options without needing to recode all your validation lists by hand. You can use information entered by the user to validate other inputs. You might have a list of people and an input cell for each where you can select their spouse. Use the whole list as the source for the Spouse cell. As the users adds people, the validation lists grows accordingly.
The last bit of the puzzle is that you can even create your lookup formulae as "Named Ranges" and then just refer to the name in the Validation List area. So, only one copy to change to update all similar input cells making the process much faster and easier to manage.
That looks good and could be extremely useful to show people trying to compare data as in your example. Hadn't thought of range names as mentioned by gbentley - great idea.
I need to have a conditional format for dates greater than other dates, and they need to be compared by paired cells through two columns. Any suggestions?
The conditional formatting feature has now become more advance and convenient in Excel 2010 as compared to what we have seen in Excel 2007 http://www.techseeks.com/how-to-create-progress-bar-with-conditional-formatting-in-excel-2010.html
Hi,
I need someone to help me to check and if that part is not there in excel it should skip that part and perform the rest of the action. Please help.
I need someone to help me to check and if that part is not there in excel it should skip that part and perform the rest of the action. Please help.
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
