Excel - Copying Only Certain Cells to New Workbook

[shelby55]

Hello

I'm using Excel 2003.

I have one worksheet in a workbook called HSMR running calculations to determine inclusion in a calculation. There is a column called diagflag that will calculate as "Y" or "N".

On another worksheet, I want to pull data over from the HSMR worksheet based on the diagflag column but I don't want to pull over any that have "N". BUT I also don't want any spaces for the cells where there isn't data.

For instance, if I used the formula:

=IF('HSMR Validation'!CY2="Y",'HSMR Validation'!A2,"")

then there will be a blank row for any where there is "N" but I only want data with "Y" excluding the blank rows for those with "N".

Can anyone help? Thanks.

 

[Lilliabeth]

I don't know what "pull over" means, but in your title, you say Copy, so...

Filter to display only the rows where that column = "Y"
Then follow these steps to copy visible rows only

http://www.dataschenk.com/Newsletters/Tips/ExcelPasteWithoutHidden.htm

--Lilliabeth

 

[SkipVought]

or use MS Query to PULL the data into your other workbook, based on your Y criteria.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Sorry but I had a brain freeze today - I meant worksheet, not workbook and I should have known to use the MS Query as Skip suggested because I've done it so many times before for other projects.

Using MS Query did as I wanted but thanks to both of you so much for responding!