Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Countdown to zero then close the option

0

We have an online/ virtual  signup in our community garden in Feb asking  our 140 members to volunteer for one week out of 28 weeks/dates. We need 4 gardeners per week. We want to have them signup and choose one of the weeks but make a date " unavailable " if there are 4 who  have already signed up .

 We tried one last year but it didnt work. One week we had  8 volunteers but in August we had zero. It was a mess!!. Help!!

 I can send the spreadsheet with the formulas for your review and correction.

Hi Will ! I have attached the  form we use for signup. The Response tab is the mail screen that the gardeners see when the go to the site.There is column for choosing you work day and Chore week.The other 2  tabs were supposed to do the countdown but they bombed and it was a mess.

Your formula looks perfect  and just trying to figure out how to link to the main sign up screen with all the other necessary information. 

Wish you were on our committee! 

excelformulas-functions
posted by: thoroughbred6 (rep: 2) on Mon Jan 3, 2022 at 4:46 pm
Answer
Discuss

Discussion

Hi and welcome to the Forum 

If you edit your original question, you can (a) use the Add Files... button to attach your Excel file and (b) modify your question to give an example of your (failed) formula - such detail is helpful and shouldn't just be in the file.

Hopefully then someone can suggest a fix.
John_Ru (rep: 6142) Jan 3, '22 at 5:31 pm
Add to Discussion

Answers

0
Selected Answer

Hi thoroughbred6,

Just as John has said, including a sample file right from the start makes providing solutions easier.

Even with the limited details I took a stab at providing a solution. Also, with 140 members and 28 weeks to cover, each member only needs to volunteer for one week and each week can have 5 volunteers ( 140/28=5 )

In my file attched:

- there is no vba required - everything is done with formulas and conditional formatting on the sheet

- when there is no name entered the cell is green, after a name is entered it turns grey

- if there are less than 5 members for the week it will indicate "Open" to the right and be green

-after there are 5 names entered it will indicate "CLOSED" to the right and be red and also have in the next cell "Select a different week" and be yellow

- after there are 5 names and the week is CLOSED, the "Wk" and "Date" cells turn grey

Cheers

posted by: WillieD24 (rep: 547) on Tue Jan 4, 2022 at 9:00 am
Discuss

Discussion

Looks great ! Can I send you the form so you can see where we would link the form to the sign up sheet? How do I do that as an attachment?
thoroughbred6 (rep: 2) Jan 4, '22 at 11:15 am
Attaching your file is easy.
Go to your original post (after being logged in) and after clicking in it you will see at bottom right "Edit". Click this. Now go to the bottom of your post and you will see at the left 3 "Choose File" buttons - these are used to attach Excel files. Click the first one and browse to your file and add it. Make a notation in your post that it has been updated & the date stating that the file was attached.
WillieD24 (rep: 547) Jan 4, '22 at 12:06 pm
Good one Willie, nice spreadsheet! Looking forward to see how it gets linked to the existing form.
John_Ru (rep: 6142) Jan 4, '22 at 12:35 pm
Hi John and Willie. Just sent original sign up form that we used last year. The count/ close function  didnt work with what ever formula we  used. 
thoroughbred6 (rep: 2) Jan 4, '22 at 3:19 pm
I'm glad you like the file I made.  

After downloading your file and reviewing it I am left with a lot of questions.

Firstly, the workbook is password protected at the VB Editor level which prohibits me from attempting some changes. While in the Editor window I see your file has no VBA.
I am guessing that "Workday" and "Chore Week" are different activities because the column headers have 25 ppl and 4 ppl respectively. Are you hoping to place restrictions on both or just the "Chore Week". The order of the information is not the order I would have it, and you have a what appears to be a duplicate column, a blank column, and a wrong header for one column. Row 1 (header row) can be improved by increasing the height to 35 (or more) and formatting all cells as "Wrap Text". Font size will need to be 10 for the long headers.
How are the fields (cells) of the sheet "Form Responses 1" populated? There are no formulas on the sheet - so an external macro? Does this info come from another worksheet that you have not provided?
On the other two tabs the "#REF!" error is due to the sheet reference missing in the formulas in "B2", "B3", and Column "H". ex: "!2:2" needs the sheet name in front of the " ! ". On sheet "680500895" the are 35 weeks listed but in your post you say there are only 28.
Unfortunately, at this time, I cannot help further without more info.
WillieD24 (rep: 547) Jan 4, '22 at 8:21 pm
Willie. I don't think sheet "Form Responses 1" is "...password protected at the VB Editor level" but rows 2:142 are hidden. I mean that I could add a quick macro (run with that sheet active) which revealed them:
Sub Unhide()
For n = 1 To 142
    Rows(n).Hidden = False
Next
End Sub
then realised that they were filtered (on column R, with filter symbols not displayed). My quess is that it's populated from a website where timed responses are made but don't I know how (presume there's a Excel sheet behind that, linked by Power Query perhaps).

You're right about #REF errors e.g. the formula in B2 of 680500895 should read (in my opinion):
=MATCH(B4,INDIRECT("'" & B1 & "'!2:2"),0)
and B3:
=INDIRECT("'" & B1 & "'!R1C" & B2,0)
but the challenge is to link limits like your answer provides with the web form / sheet.
John_Ru (rep: 6142) Jan 5, '22 at 9:26 am
Hi John
It's not sheet "Form Responses 1" which is password protected at the VB Editor level - it is the workbook. In the VB Editor window click on ThisWorkbook then in the properties window scroll down to Password and WritePassword you will see asterisks masking the password.

I too was fooled at first by the hidden rows then, like you, realized it was due to filtering. Rows 146 thru 186 are also hidden. I don't understand why there would be 186 rows needed when there are only 140 members.

For the "#REF" errors, I'm thinking references like ex: "!2:2" should be "Sheet2!2:2" (or whatever the proper sheet is). Also, tabs 680500895 and 1814693201 seem more complicated than needed.  

Hopefully my questions will be answered, more info provided, so you or I can come up with a solution.
WillieD24 (rep: 547) Jan 5, '22 at 11:46 am
Willie, I agree that no progress is possible without further info (which we have both requested, indirectly in my case).

I don't "see asterisks masking the password" in the file as you describe (I downloaded it again from the question to check). On #REF, my corrected INDIRECT formulae equate to the form you suggest (but using B1 which contains the sheet name)- give them a try! Not sure about member number (e.g. if they've reduced) but ribbon Data/Remove Duplicates on column B only says there are 4 duplicates in there.
John_Ru (rep: 6142) Jan 5, '22 at 12:12 pm
HI guys. Unfortunately I do not have the password to the worksheet an I may have corrupted it when I copied it. Yes there are 2 separate questions that each need to answer choice for Chore week (4 each) and Saturday Workday(25 each).
The way it worked was a google form was sent to all the gardeners( 200 total)vi email through our membersship managemebt system Aplos . The fields that the gardeners filled in on the form created this large registration sheet that I sent to you.The choosing of the weeks and Saturday are in Column R and S.
I asked the committee what happened to the lim,itations and they said it worked initially but by day 2 do signup it didnt. I really appreciate your deep dive on this because they are thinking about  using  multiple apps, Do you have an email I can send you with the test system they are proposing? I really liked last year that it was one place/ form to go to . New form may /will result in not having the required fields completed and I will be calling everyone for a month to get the information.
thoroughbred6 (rep: 2) Jan 5, '22 at 4:29 pm
I can  email the sign up form that was used last year. 
thoroughbred6 (rep: 2) Jan 5, '22 at 8:32 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login