How do I set up a function that skips a cell in a fill?

C

clubbhouse

I am trying to set up a homeschool schedule and need to have a blank cell but
contine the lessons. I used the fill handle to make the lesson increase, but
every Friday is a day for our co-op classes. How can I make this happen
without manually going in and labeling the lessons?
 
B

Bob Phillips

Maybe you could create a custom list (Tools>Options>Custom Lists) with a
nearly blank value, say a single dot, and fill using that.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
C

clubbhouse

Bob,
That would work if all of my weeks were 5 day weeks. Some of my weeks only
have a couple of days in them due to holidays. Is there a function I can use
in this function statement? =IF(TEXT(F3,"dddd")="Friday","",E9) I have row
3 with the dates I will be schooling. The true statement currently displays
a blank, the false statement is where I'm having troubles. My E9 cell says
"Lesson 3". How can I increase it to say "Lesson 4" if the date is not a
Friday? Then to piggy back it - the next date should be Monday - how then do
I increase the previous cell that isn't blank?

TIA,
Tammy
 
B

Bob Phillips

=IF(TEXT(F3,"dddd")="Friday","",LEFT(E9,FIND(" ",E9))&MID(E9,FIND("
",E9)+1,9)+1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
C

clubbhouse

That one works until the date after the blank (Friday) after that it returns
a #VALUE! error. Any other ideas? :)

You are such great help. I would have never looked at these functions.

TIA,
Tammy
 
S

Steve G

Tammy--

I believe Mr. Phillips' solution with the custom list will work. You
can set up a custom list for Monday thru Friday. When there is a
holiday on Monday, manually input Tuesday in one cell and enter the
lesson number in the cell in the next column. Then you pull down the
fill handle. Likewise when there is a holiday on another day of the
week. This is not a perfect solution in terms of no manual entries
but it will still save you time.

I prepared the following list with just a couple of manual entries
after I created a cuutom list for Monday thru Friday.

Monday Lesson 1
Tuesday Lesson 2
WednesdayLesson 3
Thursday Lesson 4
Friday Lesson 5
Tuesday Lesson 6
WednesdayLesson 7
Thursday Lesson 8
Friday Lesson 9
Tuesday Lesson 10
WednesdayLesson 11
Thursday Lesson 12
Friday Lesson 13
Monday Lesson 14
WednesdayLesson 15
Thursday Lesson 16
Friday Lesson 17
Monday Lesson 18


Steve G
 
B

Bob Phillips

I need a bit more help with the data in these cases.

If E9 is a Friday date, it works fine, but if it is not, it becomes blank.
Can you describe what formulae you have where, and what the one that give
#VALUE should refer to, and produce as its result?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
C

clubbhouse

I have E3 as a Wednesday date, so F3 will be Thursday, G3-Friday, H3-Monday,
I3-Tuesday. This is what I want to display... E9-Lesson 3, F9-Lesson 4,
G9-"blank", H9-Lesson 5, I3-Lesson 6. Currently I have manually put in E9.
The current display with the formula you sent is... F9-Lesson 4, G9-"blank",
H9-#VALUE!, I9-#VALUE!

Thank you!!
Tammy
 
C

clubbhouse

Bob,

I figured it out by adding another if e9<>"" statement in there. THANK YOU
FOR ALL YOUR HELP!!!!!!!

Tammy
 
Top