One sheet feeds another depending on data entered

Hi everyone,

The question I am about to ask could probably be sorted very easily if I were to use Access however unfortunately for whatever reason it needs to be done in Excel.

I have a worksheet which uses a red/amber/green status of mark the severity of an issue. A user will click into thier own sheet via a command button then enter the details of any problems.

My question is, if I created a new sheet called RED or Critial. Would I be able to have all the "Red" entries from the users sheets feed into this?

Any ideas of how I could accomplish this would be great. Am I right in thinking I could use the index forumla to actually display the row of data. I think my main problem is how would I actually get the information onto the "Crital" sheet in the first place.

Thanks