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

Easiest way is with a worksheet_activate macro. Each time you bring up the "Critical" sheet to look at it, this macro would quickly grab all the current "R" rows from the sheets listed.

Here's the code:

Please Login or Register  to view this content.

How to use the macro:

1. Open up your workbook
2. Right-click on the sheet tab "Critical"
3. Select VIEW CODE from the popup
4. Copy and Paste in your code (given above) into the Sheet Module that appears
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use. All you have to do is move off of "Critical" to some other sheet, then come back. When you do, the macro will gather the info for you.

This is exactly what I envisioned - I wasn't really sure if it would be possible. Thanks ever so much.

I have two more questions if that's ok. The new sheet where the data is collated is more of a like a "viewing" sheet now, yes? In that I can't update any cells from there. Is there a way in which if I were to update the cell in this sheet they would then alter the orignal sheet?

Also, on the sheet where the data feeds into I could like to always have two rows at the top however the current code clears all the row 1. I cannot figure out where or how to change the code to always keep 2 rows at the top.

Any help would be really appreciated,
Thanks again