Looking up values from another sheet and conditional formating etc

[slimchance]

Hi.


Im a very basic excel user. Though i might be able to solve my task through a lot of if/or/and and macros (i do know some programming), this would take me forever. I`ve tried googling my problems, but some of them seem to have very complex solutions. These answers are often from 2001 etc, though, so i hope the newer excel versions ( i have 2010) have some easier ways to solve my problems. I hope you can help me. I will break down my problem in smaller questions.


Question 1 - Dynamic list + lookup


This is what i have :


Sheet One
A- Fruits - Banannna, Pear
B- Colour - Yellow, Green


Sheet Two
A - Fruits (Dropdown list where i can choose between banana and pear)
B - Uses vlookup to find the colour automagically


Problems
1a How to make the dropdownlist (AND lookup) update automatically if i e.g add Strawberry and Red to Sheet One. ( i can make this work in the same sheet. It works automatically in excel 2010. I also tried using offset and counta, and still only got it to work in the SAME SHEET.
1b If i choose Banannna in the dropdown list, and later change the name in sheet 1 from Banannna to Banana, can i make the dropdown list update itself ?
1c Also- i would like to only be able to select each value once. (Only ONE Banana in sheet 2). This is , however, secondary to my other questions.


Question 2 - Conditional formatting based on hidden numerical value


Now i let my two friends Mary and Frank taste and rate the banana from 0 to 10. I now have one cell which states the name of the reviewer, and one cell with the numerical value. This cell is conditionally formatted to be filled at 10 , and empty at 0. I would like to combine these two cells into ONE. (I would like to see the name of the bananaeater in the cell (numerical value is optional)) AND would like the cell to be filled based on the numerical value (hidden or not )

I got an example file, but i dont seem to have permission to upload it. You can download it at

Free large file exchange service without size limits




I appreciate every bit of help

 

[Akashwani]

Hi and welcome to MrExcel>

For Question 1, take a look here...

Excel Magic Trick 549: Dynamic Data Validation List Drop-Down OFFSET or Table Feature? - YouTube
excelisfun -- Excel How To Videos - YouTube

Or here....

Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula | Get Digital Help - Microsoft Excel resource

For Question 2, you can concatenate (join) your two cells and use a Conditional Formatting formula.

Sample Data and Example Results...

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Name	Value		Result1	Result2	Result3		C F Formula
2	Bill	1		Bill1	Bill - 1	1 - Bill		FALSE
3	Bob	2		Bob2	Bob - 2	2 - Bob		FALSE
4	Ben	5		Ben5	Ben - 5	5 - Ben		FALSE
5	Bud	10		Bud10	Bud - 10	10 - Bud		TRUE
6	Dan	8		Dan8	Dan - 8	8 - Dan		FALSE
7	Don	0		Don0	Don - 0	0 - Don		FALSE
8	Den	4		Den4	Den - 4	4 - Den		FALSE
9	Su	7		Su7	Su - 7	7 - Su		FALSE
10	Sid	3		Sid3	Sid - 3	3 - Sid		FALSE
11	Sue	8		Sue8	Sue - 8	8 - Sue		FALSE
12	Tim	9		Tim9	Tim - 9	9 - Tim		FALSE
13	Tom	10		Tom10	Tom - 10	10 - Tom		TRUE
14	Ted	0		Ted0	Ted - 0	0 - Ted		FALSE
15
Sheet5

You can see from the above Results that I have "joined" the two cells using &, you can use any combination of cells and characters that you like.

The formula in H2 can be used for your Conditional Formatting, just change D2 to the cell reference that holds your data, you can also change the 10 to any number you choose.

I hope that helps and good luck.

Ak

 

[bernard_kusel]

Hello @slimchance,

I'll answer your Question 1 for now.

1a - The validation list does not accept formulas, so you'll have to do a simple trick here:
Define the fruit range as a Name (use Name Manager in the Formula ribbon), for example "Fruits"
Edit the name and change the reference from something like Sheet1!$A$2:$A$4 to
Sheet1!$A$2:OFFSET(Sheet1!$A$2,COUNTA(Sheet1!$A:$A)-2,0)
In the data validation option, instead of using =Sheet1!$A$2:$A$4 as your list, use =Fruits
Now your combobox is "dynamic"


1b - The combobox will update itself. If you had Banannna on cell A2 of sheet2 and update Banannna to Banana in your sheet1, cell A2 in sheet2 will not update. What updates is the combo box.

1c - You can't have more than one validation rule on the same cell. So you can't have combo box AND single values at once. You could do this using VBA.
If you don't want repeated values on column A, for example, you can insert the following custom Data Validation condition after selecting column A:
=COUNTA($A:$A,A1)<2

 

[slimchance]

Hi!
Thank you both very much for your reply. I`ve managed to do both 1a and 1c in excel. I`ve also found a way to work around my 1b.

I still got some trouble with 2 (and 3, which is only in my example file).). To clarify what my problem is ; The input of data itself is no problem, i do it through a java program, and can easily put the number and text in the same cell. When i have numbers and text in the same cell, however, i cant figure out how to fill the cell just a certain amount, based on the numerical value contained. I would like to define a range, say 0-10. If the number is 5 i want the cell to be half filled, 10 fully filled etc. Now i only manage to get either fill or no fill.

The last question, is if i have several of these cells with both text and numbers, how can i find the index of the cell with the highest number ?

 

[Akashwani]

Hi,

I'm sorry, but I have no idea how you can achieve the Conditional Formatting that you want. This may be possible using VBA, but I'm sure you can only "part" fill a cell by using numbers and Data Bars.

Does this help for your final question?...

Excel Workbook
	A	B	C	D	E	F	G
1	Name	Value		Result		10
2	Bill	1		1 - Bill		Results
3	Bob	2		2 - Bob		D4
4	Ben	10		10 - Ben		$D$4
5	Bud	2		2 - Bud		10 - Ben
6	Dan	8		8 - Dan
7	Don	0		0 - Don
8	Den	4		4 - Den
9	Su	7		7 - Su
10	Sid	2		2 - Sid
11	Sue	8		8 - Sue
12	Tim	9		9 - Tim
13	Tom	2		2 - Tom
14	Ted	0		0 - Ted
15
Sheet5

I'm sorry that I couldn't have been of more help to you.

Ak

 

[slimchance]

Hi!
Thank you very much for your help . I`ve tried different visual styles, and found one my colleagues also think look nice. The other problems is solved thanks to you two . Thanks again

 

[Akashwani]

Hi,

I'm pleased that you have managed to resolve your problems and thanks for the feedback.

Good luck.

Ak