Excel: Copy cell content only if certain text exists

Hello,

In the summary worksheet :

=if(Detailed!f1="completed",detailed!h1,"")

Copy down.

Cheers, teylyn

Hi,

Thanks for the speedy response. Doh, that formula works fine for the silly question that I asked & I should have asked the question with more clarity.

I now have blank rows on Summary wsheet where the answer is False. I'm thinking I'm going to have to resort to VBA to remove the blank rows. Correct?

It's been a while since I've been into VBA & am rusty, but will reluctantly venture back. I suppose I have to look for blank cells & delete those blank cells. (They reside in column B in Summary wsheet.)

Can you help please?

Not for points.  Primarily because I think it was teylyn that taught me this one ;)

Try this - option/bullet 4.  You can do it with a couple keystrokes.

http://www.theexceladdict.com/_t/t031008.htm

Dave

Well, thing is that the cells are not really blank, they contain a formula, so F5 > special > blanks will not pick them up.

Still this can be done without VBA, but with an Array formula. Starting in row 2 of the Summary sheet:

=IFERROR(INDEX(Detailed!$H$1:$H$22,SMALL(IF(Detailed!$F$1:$F$22="completed",ROW(Detailed!$F$1:$F$22)),ROW()-1)),"")

This formula cannot be copied down. (Well it can, but then it does not do its job :)) Instead, select the whole range of cells where you want the formula, enter the formula into the first cell and hit Ctrl-Shift-Enter.

In the attached file you see the If() formula in column E on the summary sheet. It shows the gaps that you don't want.

In column G, the yellow cells contain the array formula. I selected G2 down to G22, entered the formula into G2 and hit Ctrl-Shift-Enter. You can not change any single cell in the yellow range. If you want to edit the formula, you have to select all cells with the formula, make the edits and hit Ctrl-Shift-Enter again.

see attached.

Cheers, teylyn
array.xlsx

That's cool, Dave. Never worked with the "logicals" before. I must remember that.

The drawback, though, is that it is not dynamic. When the "Detailed" sheet data changes, you would have to copy the IF() formula in the "Summary" sheet down again and repeat the deletion routine, because

- if a value in the "Detailed" sheet changes from "Completed" to something else, then a "False" will appear in the "Summary" sheet.
- if a value in the "Detailed" sheet changes from something else to  "Completed", it won't show up in the "Summary" sheet at all.

So the user needs to be on her toes and manually process the "Summary" sheet after data changes on the "Detailed" sheet.

The array formula I suggested, although a bit unwieldy, will reflect the changes in the "Detailed" data instantly, without any further intervention. Depending on the data volume it may take a couple of seconds to calculate, but it will still be faster than manual intervention.

cheers, teylyn

Yep

LOL

Hi,

@Dave: Thank you, I learnt from your ideas.

@Teylyn: I was getting #NAME? error. So I then learnt that that is because I'm using Excel 2003. Itried fixing it with ISERROR but now I get too many arguments problem. I don't completely understand what I'm doing, but this is what I changed it to & now I get "Too many arguments for this function" popup box:

=IF(isERROR(INDEX(Detailed!$H$5:$H$22,SMALL(IF(Detailed!$F$5:$F$22="completed",ROW(Detailed!$F$5:$F$22)),ROW()-1)),""))

Thanks Teylyn. The results from your latest formula isn't producing anything. I've been trying to figure out why, but to no avail.

Thanks Rob. I will take a look at your idea now to see if I can get the result I want & get back.

I am doing this in 'fits & starts' & will try spend time on it within the next couple of hours.

It might be useful if you upload a sample of your workbook, as we're all visualizing what you're trying to do and you're working behind the curtains ;)

Dave

>>Thanks Teylyn. The results from your latest formula isn't producing anything. I've been trying to figure out why, but to no avail.

You need to select the whole range, enter the formula into the first cell and confirm with Ctrl-Shift-Enter. I explained that in my second comment above

See attached with the 2003 version of the formula in H2:H22

cheers, teylyn
array--1-.xls

Hey Teylyn, you beauty, it worked! (in my haste, I forgot about the Ctrl-Shift-Enter, sorry).

Thank you all for your great input. I have learnt from all.

Thanks for your patience with my slow responses, I am delivering training (not excel!) & try to fit this in between training sessions.

I must say, I think you experts are fantastic with your responses.

gotta run but will close & accept this question next break.

I love the fast & quality responses.:-)