insert text if criteria met

**freshstart** · 08-22-2008, 09:50 AM

Hello everyone,

Can anyone help me please?

I need a formula that will look between columns AX:BI for the amount of 500 and if this criteria is met to then copy the text from AE to where the formula is on a different sheet (same workbook).

I would prefer for all the returning text to read as a list with no blanks.

In other words i would prefer to not have a list full of blanks where there has been no 500 in AX:BI.

To quickly explain the relevance:

AX:BI shows funding, these are various amounts but the 500 is the startup grant for the business. there is no text in these columns. If there are blanks then the business did not get a grant to startup or have just enquired.

AE is the type of business that is intended to be started. This is always text.

Any ideas guys? once again thanks for your time and help.

**NBVC** · 08-22-2008, 10:05 AM

Try something like:

=IF(ROWS($A$1:$A1)>COUNTIF(Sheet1!$AX$5:$BI$1000,500),"",INDEX(Sheet1!$AE$5:$AE$1000,SMALL(IF(Sheet1!$AX$5:$BI$1000=500,ROW(Sheet1!$AE$5:$AE$1000)-ROW(Sheet1!$AE$5)+1),ROWS($A$1:$A1))))

you will need to adjust sheet name and ranges to suit.. (don't change this part though...ROWS($A$1:$A1) )

Then you must confirm the formula with CTRL+SHIFT+ENTER not just ENTER.. you will see { } brackets appear.

Then copy the formula down as far as you desire...

Note: You cannot use whole column references, like AE:AE in this formula...

**freshstart** · 08-22-2008, 10:17 AM

NBVC,

Thank you once again! That is just amazing!

And all within 15mins!

There are no blanks or complications.

**freshstart** · 08-22-2008, 10:20 AM

Just a quick query,

The number 1000 next to 500 in the formula, what is that for?

**NBVC** · 08-22-2008, 10:23 AM

That was my sample range size... you need to adjust that to ensure you capture all your data.

**freshstart** · 08-22-2008, 10:34 AM

Thanks again,

I have changed all 1000 to 3000 for my sample range.

**NBVC** · 08-22-2008, 10:37 AM

Note that I also start all my ranges at row 5.. you may or may not need to change that too.

insert text if criteria met

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

insert text if criteria met

Brilliant!

And thats the Cherry on top

Thread Information

Users Browsing this Thread

Similar Threads

SumProduct error when using Year , Month and text criteria

Insert Column on criteria of text input to cell

Macro to copy text certain # of times and insert new row each time

Auto insert Text into empty row

macros to insert text

Bookmarks

Bookmarks

Posting Permissions