Search results

for a given range :- =INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10))

activecell.row activecell.column

Thanks for closing this off

Wow, that's a bit scary. House fire statistics show a Heavy corelation to smoking, any smokers in your family ? When I was in the ROI a couple of weeks back a couple of volunteer firefighters (just like Zack) lost their lives. The news reported that they got 25 Euro's per call out, I'm not sure...

Good Catch Rich, I notice that on another forum you purport to be Irish, is this true ?

you'd need to monitor selected cells, and when a 6 digit number is entered covert that to a date using a worksheet/workbook event. The problem start arising when your number starts with a 0.

Try using Worksheets("MainMenu").Select or ActiveWorkbook.Worksheets("MainMenu").Select or ThisWorkBook.Worksheets("MainMenu").Select (depending on which you feel is most applicable and see if you get the same result.

The only way I know of to do this is usnig code, have a button above each column your users will need to sort on and when then press it, it will unprotect the sheet do the sort and then reprotect.

As to how it works :- Sumproduct allows you to deal with ranges, more than that, it allows you to deal with individual values within ranges normally only available via Array formula, so ... ap155:ap184>0 tells you if each value is greater than zero, true = yes and false = no --() is a double...

Sorry, my mistake the "=" was not moved when I adjusted the formula =if(sum(ap155:ap184)=0,0,sumproduct((ap155:ap184>0)*ap155:ap184)/sumproduct(--(ap155:ap184>0))) You don't have an error to detect for so this is unecessary, what you did have is a crap formula (I appologise again), I should have...

if(sum(ap155:ap184)=0,0,=sumproduct((ap155:ap184>0)*ap155:ap184)/sumproduct(--(ap155:ap184>0)))

So how (programatically) do I find section 2 ? Is there a gap between section 1 and section 2 ? What does the data look like ? Can you construct a mock up and attach that ?

Try a sumproduct :- =SUMPRODUCT((AP155:AP184>0)*AP155:AP184)/SUMPRODUCT(--(AP155:AP184>0))Should work OK and is NOT an array.

So where is section 2 ?

Sorry are you asking a question ? If you want to list the product description in the model number list, then we would need the model list and the product list and we can show you how to link them. Attach a spreadsheet and we'll take a look.

Post a sample and show what you think the result should be vs what is calculated.

Yeah it is a bit stupid that you can post other file formats without compression but not .xls I, personally, think that ALL files should be zipped, to reduce up/down loads eating into both bandwidth AND (more importantly) some peoples data cap's. Also some people still have to contend with dial-up.

Well yes ... by definition you can't have a row less than 1. So when you have -y + 1 this is the same as 1 - y so even 1 will give you an error as 1 - 1 = 0, and there is no such thing as row 0. If your post #3 was what you want then I'd go with :- Sub Delete54On Dim lnA As long lnA =...

I'd guess that you need co-ordinates (a pair of) for the offset try :- .offset(1-y,0) or just include the comma, note if y = 1 or greater this will error anyway

Post a sample workbook