Search results

Hi robertSYNCREON other hint: if you want to know if a certain date is weekend you can use Weekday(serial number,1) and when you formulate. All 6 and 7 are Saturday and Sunday you can avoid in your formatting.

Hi Luke M, may be I misunderstood the question. I came up with just INDEX($A$2:$A$10,MATCH(B20,$B$2:$B$10,0),1) and he can copy down.

why don't you do a sum(all) and subtract sumif(range,"8",range)? Also when you said the number contains "8". do you count 18, 28, 38, etc? or just 8?

I don't know why either. But you can use combox box instead.

Ashley, try removing the "$" on your last message. You don't want the condition to fix at cell L2.

haha Luke M beat me by 1 sec.

Use group box for each question and choices. so you need a total of 10 group boxes.

I think you need a helper column for the pivot table. Set condiitons for a if then condition comparing the dates on each cell against today(). you may also want to use month() function in conjunction with today(), such as month(today()) to set condition for last week -7 to -14. you may want...

Thanks Luke! I think I got what you mean now. ""use you in the average if your value is a AND your value is c AND your value is d."

hi Hui, Firs tof all, thanks for the hlep. So are you saying the "criteria range" cannot come from the same column when I use SUMIFS and AVERAGEIFS? Because that is what I'm seeing on all the samples online and provided by MS help button. It would be much helpful if we can set the...

I have a set of data from A1:B4 on column A down: a, b, c, d on column B down: 2, 51, 3, 7 I tried AVERAGEIFS($B$1:$B$4,$A$1:$A$4,"a",$A$1:$A$4,"c",$A$1:$A$4,"d") AVERAGEIFS($B$1:$B$4,$A$1:$A$4,A1,$A$1:$A$4,A3,$A$1:$A$4,A4) but they are all giving me the answer #DIV/0! I'm using MS07...

you can do a countif(range,criteria)

1. column E: column C & column D 2. column F: countif($e$1:e1,e1) copy down. just filter out "1". delete the rest.

Use filter.

Try =IF(COUNTIF(B$2:B$10,0)>ROWS(A$22:A22),"",INDEX(A:A,SMALL(IF(ISNUMBER(B$2:B$10),ROW(B$2:B$10)),ROW(A1)))) crtl+shift+enter you can also extend the formula where iferror(above formula,"") to remove any #NUM! between lines 22 to 29. you don't have that many data to go to line 32.

formula more like... > today() - 14 > today - 14 and <= today() - 28 <= today() - 29

Hi ashish. there is not enough information to process in your original message. Vijay's formula is driven by entering the date you have entered and add the previous week's data with the current week's data. it works on my side. I guess you only need a simple formula where H3 = H2 + G3 and...

Thanks, Luke M.

1. use weeknum() if you have MS 2007 or after. not sure if 2003 has this function or not. 2. can you show some data sample? I don't quite understand what you need.

Thanks Luke M. I can do that. However, is there a way to let the users to click the "+" and "-" button to hide/unhide, instead of hi-lighting the rows/columns when the protection is on?

I was asked to protect certain cells in a worksheet, so that the users cannot manipulate the formula or overwrite them. That was easy. But then I was also asked to make the worksheet flexible enough to allow the users to hide rows and columns. I'm using MS 2007 and I have tried with no...

Building on Luke M's suggestion, my guess is that if a formula is changed, the result would not be the same as first intended. you can use the hidden worksheet or helper column(s) to compare the original result. if the results are different, you can use conditional formating to hi-light the...

Thanks, Luke M. I didn't realize I could use "&". now countifs is more versatile than I thought!

And what's your question?

please describe in detail.