Another Excel Question

[Kasturi]

Im trying to write a formula that looks at a column and if that column contains a certain word then I would like for it to copy a certain cell to another spreadsheet.

For example: If column C contains the word "Critical" then it will take the information located in cell C1 and place it in on another sheet. (I would assume where the formula is located)


I can get the formula to work if I specify a certain cell that contans the word "Critical", but I cannot get it to look thru the entire column.

I may need to go the VBA route to accomplish this. Especially if the word "Critical" is located in column C multiple times.

Any help would be appreciated

[Kasturi]

I will try to explain what I am needing a little better.

In one sheet I have results of an audit. Some questions could result in Critical (No - Critical)or Non Critical error (No - Non-Critical)

I take these errors and place them on a Summary tab (sheet).

I need a VBA code to search thru all columns in a given range to look for either the critical or non critical error and then copy certain information (from specific cells) onto the summary sheet.

There will be instances where one column could have more than one critical or non critical errors or a combination of both.

I would need these the code to go thru all records and copy/paste all required data to the summary sheet.


I am attaching a sample of what I am working on.

Also if not to much trouble. I would like this code to be added to a command button on the summary tab



Thanks in advance to whomever attempts to help me