Search results

Small example attached - post back if this is not what you need, if you can attach an example of your book that would make things easier Ed

Yes, you can do this using the INDIRECT function Regards

Similar formula should work - can you post an example or at least give some details of the ranges where the values may occur. Edit If you only have the word sold in one column and the word new in the other one then =SUMPRODUCT(--(your_range here="sold)*(your_range here="new")) Note: You...

No problem - you beat me to the draw on the response though! Ed

If you use search ather than find, it has the advantage of not being case sensitive =SUMPRODUCT(--(ISNUMBER(SEARCH("special needs",E2:E5000))),--(D2:D5000="Commissioned")) For a full explanation of SUMPRODUCT see http://www.xldynamic.com/source/xld.SUMPRODUCT.html Ed

Hi try =SUMPRODUCT(--(p_Network!$D$2:$D$50000<(TODAY()-56))*($D$2:$D$50000>0)) Ed

Excel security isn't designed to stop people looking at the data, more to stop accidental changes. Anyone with any significant knowledge can bypass both sheet and VB password protection If you have sensitive calculations then copy and paste special values into a copy of your sheet Ed

Brian, As usual you are right! My testing was at fault - Mea Culpa. Ed

Provided each number only occurs once, if your results were in A1:A10 Sum(A1:a10)-(small(a1:a10,1)+small(A1:a10,2)) would give you a total of the 8 highest scores. The problem comes if there is a repitition of any low score as repeated instances will all be subtracted. Ed

If you just want to remove them from calculations use SMALL

Hi there, Suggest you have a look at the attached link for data validation > dependent drop down lists and dynamic lists. http://www.contextures.com/tiptech.html You will also need to combine these with VLOOKUP. Ed

This is the best guidance around: http://xldynamic.com/source/xld.SUMPRODUCT.html I keep this on my favorites. Also a very good site is http://www.contextures.com/tiptech.html. Hope they are of some use Ed

Hi Brian, I think you are technically right - I tend to whack it in as a matter of course and it does no harm. I use sumproduct a lot - I 've developed a bit of a love affair with it :) - I presume sumifs in 2007 will make it redundant eventually :( ED

Why make life difficult! Quite agree - this is a problem just made for SUMPRODUCT From your original post the code below would work perfectly - format the cell as percentage =SUMPRODUCT(--(A1:A6>0)*(B1:B6>=A1:A6))/SUMPRODUCT(--(A1:A6>0)) Adjust ranges to suit the data and remember that...

Hi, All cells are protected by default Select cells you do NOT want to protect. Right click Format cells Protection Uncheck protected box > Ok Next go to tools > Protection > protect Only check "select unlocked cells" Enter password > Ok > Enter password > OK Example attached Ed

Another problem Lee, One problem with Vb is that it needs a 'trigger' to make the code run - I am not sure if streamed information will do this. My VB knowledge is pretty limited, I'm afraid, so I'm not going to be much use there! It might be worth posting in another forum - I would suggest...

OK, As I understand it: In J5 If A5 =I5 and if C5 doesn't contain "T", use C5, otherwise use something else - as yet undefined to make this independent of the rows =If(and(indirect("A"&Row())=Indirect("I"&Row()),ISERR(search(indirect("C"&row()),"T")),Indirect("C"&row()),What data do you...

What cell is this formula going to go in? ed

Hi Lee, What I was after was seeing what the checks were so as to see what the problem is................ =if(correct data,INDIRECT("A"&ROW()),problem) is the correct syntax. can you give me the actual formula and the result that you are getting? I don't see where the A3 and K3 you...

can you zip the sheet and attach it - it's much easier to make sense of it. Ed