Information summary

[chris007]

Hello again Folks,
I have an issue that may or may not be able to be resolved if you can help that would be appreciated.

I have a workbook with 5 Sheets, one sheet is Data Sheet and one sheet is a summary sheet.

what i want to happen is if column Q row 5 in the "data sheet" has a value of =>30 place the contents of the row into the "summary sheet" row (for this exercise row 5).

i need this to happen for all rows in the "data Sheet" where the value in column Q is =>30 to be placed into the "Summary Sheet" and in hteir individual rows.

Is this possible?

Thank you for your assitance in andvance

regards
chris007

 

[paddydive]

can u explain again.... its quite confusing...

 

[chris007]

OK I will try to explain hopefully.

I want all the information in row 5 of the "Data Sheet" where cell Q5 is => 30 transfered into the "Summary Sheet"

The number of columns in the "Data sheet" is from Column A to Column U.

I guess I then copy the formula down per row to import the rest of the data from all other rows in the data sheet where cell Q.. is ->30 into the Summary sheet?

There are around 32 rows with data of various values in column Q butI only want the values above or equal to 30 to be inported.

Does this help or make it easier? Hope so sorry for the confusion

Regards
Chris007

 

[obiron]

I have a workbook with 5 Sheets, one sheet is Data Sheet and one sheet is a summary sheet.

what is in the other three sheets?

you can copy the data from one sheet to another automatically using the Worksheet_OnChange() event. This can be programmed to look at the rows or cells and decide what to do based on a value.

What happens if you edit the details sheet and the value is no longer above 30.
What happens if you change the threshold to 25?

 

[chris007]

what is in the other three sheets?

you can copy the data from one sheet to another automatically using the Worksheet_OnChange() event. This can be programmed to look at the rows or cells and decide what to do based on a value.

What happens if you edit the details sheet and the value is no longer above 30.
What happens if you change the threshold to 25?

Hmm sounds like it can be done but not certain how

The other sheeets just contain look up data and tables for the main data sheet.

What i want to happen if the initial scored value is below 30 then the information remains on the "data Sheet" and does not get transfered to the S"Summary Sheet".

Interesting point you raise because if the value on the Main "data Sheet" drops below 30 because of a review, then I would want the information to be removed from the "Summary Sheet" but remain on the Main "Data Sheet".

does this sound confusing?
Regards
Chris007

 

[obiron]

without writing the code this is what I would do:

When the worksheet_changed() event fires for the details sheet I would:

• delete the data from the summary sheet
• filter the data on the details sheet based on whatever criteria were needed
• Copy the unfiltered data
• Paste Special -> values into the summary sheet.

This is a lot easier if you only have one details sheet, hence the question about the other sheets.

if you turn off screen updating whilst this is happening and then turn it back on when you are finished (making sure you are back on the correct sheet). If you write it without Selections (i.e. referenced ranges only) then it should be almost instantaneous, depending on the number of records in the details sheet.

 

[paddydive]

Hi chris007,

I think a simple way to do it is use the Advance Filter and done.

Check the link if you have any doubts about it

http://www.contextures.com/xladvfilter01.html

Hope this works for you.

Pradeep