***WINNERS ANNOUNCEMENT*** June/July 2008 Challenge of the Month

[cornflakegirl]

Re: June/July 2008 Challenge of the Month

Do you have a header in D1? If so, your COUNTA will include this row, so your range will be 1 row bigger than needed, and include a blank row at the bottom.

 

[robind21283]

Re: June/July 2008 Challenge of the Month

Is that why I'm getting a 0 result? It doesn't seem like that would matter (for this particular example)

 

[Richard Schollar]

Re: June/July 2008 Challenge of the Month

Yes, it will end up returning the empty row (which Excel will interpet as having a value of zero).

 

[robind21283]

Re: June/July 2008 Challenge of the Month

So, am I better off deleting the header, or adding a -1 to my formula, or something else all together?

Thanks for all your help!

 

[cornflakegirl]

Re: June/July 2008 Challenge of the Month

Change the COUNTA range?

 

[robind21283]

Re: June/July 2008 Challenge of the Month

I got it to work using CountA(Sheet1!$D:$D)-1

If I change the range, then I would have to pick some arbitrary place for the list to end no?

Thanks!

 

[Richard Schollar]

Re: June/July 2008 Challenge of the Month

You've done exactly the right thing. You could have changed the range to start at D2 but you are rthen correct that you need to choose an arbitrary place to end the range (altho this could be D65536 if on xl2003 or below).

 

[cornflakegirl]

Re: June/July 2008 Challenge of the Month

Just make sure that no-one deletes the header

 

[pma]

Re: June/July 2008 Challenge of the Month

I pasted =LOOKUP(2^15,SEARCH(D$2:D$10,A2),E$2:E$10) into b2:b25. I then entered a different phrase including one of the colors into a26. When I hit enter the formula from b25 was copied down to b26, which was blank, automatically. I don't see any macros at work. What's going on here?

 

[barry houdini]

Re: June/July 2008 Challenge of the Month

Hello pma, welcome to MrExcel.

This is an Excel feature (certainly in 2003), see this from Excel [2003] Help. See red highlighted section

<TABLE class=OTbl cellSpacing=0 cellPadding=0 width="100%"><TBODY><TR><TD class=cdOTATtl width="100%">Extend formats and formulas to additional rows </TD><TD style="PADDING-RIGHT: 0px; PADDING-LEFT: 12px; PADDING-BOTTOM: 0px; PADDING-TOP: 0px"></TD><TR><TD class=ACB style="PADDING-RIGHT: 12px; PADDING-LEFT: 12px; PADDING-BOTTOM: 10px; PADDING-TOP: 10px" width="100%" colSpan=3><!-- defs in --><!-- META NAME="lcid" CONTENT="1033" -->By default, Microsoft Excel automatically formats new data that you type at the end of a range to match the preceding rows. Excel also automatically copies formulas that have been repeated in the preceding rows and extends them to additional rows.
Note In order to be extended to new rows in the range, the formats and formulas must appear in at least three of the five preceding rows.
You can turn this option off (or back on again) at any time:

1. On the Tools menu, click Options.
2. On the Edit tab, clear the Extend data range formats and formulas check box to turn automatic formatting off. To turn automatic formatting back on again, select the Extend data range formats and formulas check box.

<META content=03/24/2005 name=MajorRevDate><META content=08/15/2003 name=CreationDate><!--@@FEEDBACKWIZ@@--></TD></TR></TBODY></TABLE>