Information summary

chris007

Board Regular
Joined
Jun 27, 2011
Messages
82
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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

chris007

Board Regular
Joined
Jun 27, 2011
Messages
82
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

Active Member
Joined
Jan 22, 2010
Messages
469
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

Board Regular
Joined
Jun 27, 2011
Messages
82

ADVERTISEMENT

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

Active Member
Joined
Jan 22, 2010
Messages
469
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,187
Messages
5,594,753
Members
413,930
Latest member
Nela817

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top