Recent content by DaveCon

Without using VBA the only option is to have a helper column, either one that turns your number into an alphanumeric string for example in the cell next to the customer number (assuming data starts in A2) have a formula of ="A"&A2 and then filter on that column using the custom filter option of...

If i've understood you correctly then all you need to do is replace the formula in column H with the below, it warns of a circular reference but it doesn't actually exist, it's just because part of the vlookup range would create a circular ref if it was used but it's not, it's just a column in...

Only way i can think of without using VBA is to wrap each cell you want cleared simultaneously in an if statement i.e. if(A2="","","Usual Value") or if(isblank(A2),"","Usual Value"). Although, this wouldn't be practical if the cells you want cleared are manually entered values, in that case you...

S_K_S, Enter the below into your targets column and copy down, if you're using later than excel 2003 then you can also use the countifs function but i'm restricted at work to 2003 so can't test the syntax for it =SUMPRODUCT((Actual!$A$2:$A$65>=Target!A2)*(Actual!$A$2:$A$65<=Target!B2))

Assuming first Item Code is in A3; =if(left(A3,1)="G", "Generic",if(A3="","Missing","Normal")) I would suggest having Item code in one column and the status in the other though so you can just drag down the formula instead of a lot of copying and pasting which would be neccessary when the...

Thanks Hui, From what you;ve said about not kicking in until releasing the scroll bar it would appear i'll just have to stick with the conditional formating or think of something. Thanks for taking the time to respond

Thanks for the reply Hui, I've already added in conditional formatting to do as you've suggested however the numbers being dealt with are typically in the hundreds of thouands and while I have the incrementation set to 1,000 it still requires to go by ones at times to get to the exact number...

Hi, I was hoping someone could help me, essentially i have a couple of scroll bars to flex certain numbers and I would like to have it so that a message box pops up when the sum of the flexed numbers matches the target, the sheet is set up as below Target Flex 1 Flex 2 Flex 3 Total (sum...

Hi all, I'm Dave, a data analyst in Warwick, found this site a couple of months ago just looking for faster ways to do things and has now become one of my most visited sites.

Finished playing, all that was needed was to subtract 1 on the final condition/calculation of the formula so D3: =IF(OR(Start&#62;E2,End&#60;D2),0,(E2-D2)-IF(Start&#62;D2,Start-D2,0)-IF(End&#60;E2,E2-End,0)) becomes D3...

Thanks Hui, There's a few little kinks to work out (results always leave one quarter a day short while the other one is fine) but the above has given me an excellent starting point and all of the middle and a fair bit of the end :) so many thanks for taking the time to look at this In case...

Hi all, I have a spreadsheet where there are two dates (start and end) and from these I need to calculate how many days between the two dates are in each quarter. I've found a couple of issues doing this as the two dates can either span 2 quarters or be 1 month within the middle of a quarter...