Lookup then RAG status

[ngocpdn]

Hi all, Need some formula help (i think)

I need a RAG dashboard lookup formula, its based on a list of text. Ive tried a few things including custom number & index match but it doesnt seem to work.

Any idea on how i can do this? I have this list of data:

Name	Testing	Approvals	Reviews
project 1	Completed	Not started	At Risk
project 2	WIP	Completed	At Risk
project 3	At Risk	WIP	WIP
project 4	Not Started	Not started	WIP

Lookup project name, in each colum check the status & give the appropriate circle colour. Should look like this:

Name	Testing	Approvals	Reviews
project 1	●	●	●
project 2	●	●	●
project 4	●	●	●

Red = At Risk, Amber = WIP, Green = Completed, Grey = not started

Thanks in advance for your time

 

[Dan_W]

So to clarify, you have a table of data, from which you have produced a second table of coloured circles. Each circle's colour depends on the text in the table of data. So in your second table above, you've omitted Project 3 - would this then be red, amber, amber?

Wouldn't you be able to accomplish this with some circles from the Symbol on WingDings fonts and by applying conditional formatting?

 

[ngocpdn]

Hi Dan_W

Yes you're right if i wanted it for every project on the list.

The added layer i need is for the table2 (RAG Dashboard) to only lookup projects in the list & show a colored dot depending on the status in table 1. Thats why i skipped Project 3.

Im sure the solution is just as simple as you've suggested but i cant work it out

 

[ngocpdn]

i finally worked it out!! ?

Index Match + Custom Number + Conditional format solved it.

woohoo. thanks all for giving this a look