Search results

Another way without using a formula.... add a -1 to any empty cell and copy that cell. Select the numbers to convert, and then go to Edit|Paste Special (or, in 2007+, right-click, Paste Special) and select Multiply. Click Ok and done. Now you can delete the -1.

A sample workbook showing what you have/need would be useful.

Try something like: =SUMPRODUCT(SUBTOTAL(9,OFFSET($B$1:$B$100,ROW($B$1:$B$100)- MIN(ROW($B$1:$B$100)),,1)),--($A$1:$A$100=X1)) where A1:A100 contain the part numbers and X1 contains part number to match in filtered list... and B1:B100 contains numbers to sum up.... Adjust ranges to suit.

You don't need even the SUM() function to return the numbers.... In the comments box (cell A18), try: ='[Month End Sample.xls]Depot'!$C$10 If the comment is empty you will get a 0, so you can use an IF function to return a blank when empty: =IF('[Month End...

You can also use MOD e.g. =A1+IF(MOD(A1,1)>0.512,0.512,0)

That's not a lot to go by. Perhaps posting a sample workbook showing what you have (no confidential information), and what you want to accomplish would help. Also, I would suggest using better titles that better describe the issue instead of posting your name as a title. It is not beneficial...

Have a look at similar question answered here: http://answers.microsoft.com/en-us/office/forum/office_2007-excel/excel-2007-pivot-table-drill-down-double-clickon/0c7a5a6f-0903-46cc-be49-e3dd290dd940

Try using INDIRECT() e.g. =SUM(INDIRECT(AC4&S4&":"&AD4&S4)) IF AC4 and AD4 are holding "C" and "E", respectively to indicate Columns and S4 is holding a number to indicate the row...

try selecting the column of dates and then go to Data|Text to Columns. You can click Finish and it should be back to integers....that you can format as Date.

No problem, I added another formula in case the washes are not constant...

If there are always 5 "W's" and then an "R", then perhaps this? =COUNTIF(D3:BD3,"W")-COUNTIF(D3:BD3,"R")*5 If you are not consistently 5 "W" and one "R", then try: =IF(COUNTIF(D3:BD3,"R"),COUNTIF(INDEX(D3:BD3, MATCH(2,INDEX(1/(D3:BD3="R"),0))):BD3,"W"),COUNTIF(D3:BD3,"W"))

Can you post a small sample workbook to show what setup you have and how you are entering these "R's"?

Not sure if that formula in Post 2 works correctly.... Try: =TEXT(A1,REPT(0,10)) where A1 is first cell to convert, then copy down

You are welcome... Yes, I should have included that... :cool:

Try this formula in AB2: =INDEX($C$1:$AA$1,MATCH(TRUE,SUBTOTAL(9,(INDIRECT(TRANSPOSE("c" & ROW()&":"&ADDRESS(ROW(C2),ROW(INDIRECT("1:"&COUNT(C2:AA2)))+2)))))>B2,0)) confirmed with CTRL+SHIFT+ENTER not just ENTER so that you get { } brackets appear around the formula and then copy down.

In Sheet1, C2 enter formula: =VLOOKUP(A2,Sheet2!A:B,2,0)=B2 copied down and in Shee2, C2 enter formula: =VLOOKUP(A2,Sheet1!A:B,2,0)=B2 copied down TRUE result meets the matches are identical. FALSE result means there is a mismatch in the other sheet... You can filter (using...

Try: =SUM(--(FREQUENCY(IF(($A$2:$A$1674>=DATE(YEAR($A2),MONTH($A2)-11,1))*($A$2:$A$1674<=DATE(YEAR($A2),MONTH($A2)+1,0))*($C$2:$C$1674=C2)*($D$2:$D$1674=D2),IF($B$2:$B$1674<>"",MATCH($B$2:$B$1674,$B$2:$B$1674,0))),ROW($A$2:$A$1674)-ROW($A$2)+1)>0)) confirmed with CTRL+SHIFT+ENTER and copied...

The data validation drop down menus are very limited as to the formatting. If formatting such as font, row count, etc are important then you need to revert to comboboxes... I think this is what Galaxiom was referring to when he stated I gave the "right" solution... :)

You can have more then 8 rows displayed in Excel 2003. The way to create that option is different depending on which controlbox you used. If you used the Controlbox from the Forms toolbox, then right-click the controlbox and select Format Control, then in the Control tab, adjust the Drop down...

Using INT(A1) will cut off the time portion... but if you format the cell with date/time, then you will see a "time" portion of "0:00" or similar... The TEXT() function will give you a text string and so will not really be a date... and converting it to a date with say +0 to coerce it will make...