Selecting Every 3rd Cell In A Formula

[Craig11]

I am using the following formula, but I don't want a set of cells, I want every 3rd cell - $B$3, $B$6, $B$6, etc. How would I do this?

=IF(E2="","",IF(COUNTIF('Roster'!$B$3:B$80,E2)>-1,"",ROW()-1))

Thanks,
Craig

[XOR LX]

Hi Craig and welcome to the forum.

If you don't mind me asking, I'm a little confused about some of the logic in your formula:

You are using a COUNTIF statement on a range of cells with the condition >-1. However, since the minimum return for any COUNTIF is 0, you're effectively achieving nothing with this condition. What exactly did you intend with this part of your formula?

(edit: this section deleted - reason - incorrect statement)

Regards

[Craig11]

Attached is the file. Under each day, I have a drop down list. I would like the list under the same day to not display a selection if previously used for that day. For example, if 4501A is on Monday (line 3), then on line 6 under Monday 4501A would not be available. 4501A can be available across for each day of the week, but only once per day.

Craig

[L-Drr]

not sure how to implement this, but you can use the MOD function combined with the ROW function: only count value if MOD(ROW(...),3) = 0

[Craig11]

I was using information from another thread that I located.

[Craig11]

How would I use this in my file?

not sure how to implement this, but you can use the MOD function combined with the ROW function: only count value if MOD(ROW(...),3) = 0

[L-Drr]

not sure how to implement this

Sorry this meant "I don't know"
But I'm sure someone else could step in here...

[martindwilson]

cross post @
http://www.mrexcel.com/forum/excel-q...l-formula.html

[protonLeah]

Created Named Ranges for weekdays only:

Monday := "='bens lookup table'!$B$3:$B$82"

For the morphing list I used: "=IF(ISERROR(MATCH('bens lookup table'!$A3,Roster!B:B,0)),$A3,"")"

For Data Validation Formula (Monday): =INDIRECT(B$3)