Asking a ? in a formula

MInConst

New Member
Joined
Jul 12, 2009
Messages
21
I have a worksheet for my wife that she needs to fill out weekly for work. It contains 3 cells that I want to automate. My problem is that some of the date required changes at times. I will explain.
One cell needs this.. (Daily Goal Quota) = "Remaining monthly goal ÷ # of days left in the month."
The remaining monthly goal is derived by subtracting a changeable goal - what she has done to date which is in another cell already. She gets this from a printout. So the changeable goal needs to be input into this formula as a question.
Another cell needs.."Daily Goal Quota x days in that week"
The Daily Goal Quota is now a known from above but the days left in the week changes hence a question in the formula.
So my question is.. Is it possible to add a question to a formula? Like when she gets to the cell a pop up, or something, comes onscreen and asks her for the quota or days left in the week as needed.
I hope I've made this understandable. Thank you for your help.
 
Last edited:

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You would find it a lot easier to simply use two input cells for those values and then refer to those cells in your formula. Otherwise, you would have to use code.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,232
Office Version
  1. 365
Platform
  1. Windows
Why does the no of days left in the week need to be entered manually?

Couldn't it be calculated using a formula?
 

MInConst

New Member
Joined
Jul 12, 2009
Messages
21
royya, This sounds good. I was thinking about this way but hoped a question could be used.

Norie, I guess the number of days in the week could be done in a formula but I don't know how to build that formula.

Thanks for the help.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

You could use a question using VBA, but it's bad worksheet design for what you describe, particularly since you said the goal number had already been calculated in a previous cell, so you should just refer to that cell.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,232
Office Version
  1. 365
Platform
  1. Windows
I'm not so hot on formulas myself - but I know quite a lot of people who are, and they frequent this board.

Perhaps if you explained further what you are after they can help.

How are you defining the no of days left in the week? Which day does your week end on/begin?
 

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919

ADVERTISEMENT

Is there a monthly goal which is equally divided evenly between the number of weekdays she has done?

For instance this month we have 21 work days (forgetting we're in a holiday period) does she have a specific number of tasks set at the start of each month to split between those work days? Would it be the same number of tasks per day? Or is it the same amount of tasks per month regardless of how many workdays she has? It would make life easier for those trying to come up with a formula if you could add some details

Can she complete all tasks ahead of schedule? if so does she get more added and how would that affect her target?
 

MInConst

New Member
Joined
Jul 12, 2009
Messages
21
Is there a monthly goal which is equally divided evenly between the number of weekdays she has done?

For instance this month we have 21 work days (forgetting we're in a holiday period) does she have a specific number of tasks set at the start of each month to split between those work days? Would it be the same number of tasks per day? Or is it the same amount of tasks per month regardless of how many workdays she has? It would make life easier for those trying to come up with a formula if you could add some details

Can she complete all tasks ahead of schedule? if so does she get more added and how would that affect her target?

Scottylad2,
Her goals are all $. This is a beauty salon she manages. She is given monthly money goals for service sales and another for retail sales. These goals change monthly. She does surpass her goals but the goals don't change until the next month.
She has to use month to date figures as well as week to date figures that she gets off her POS system. The week to date is normally 7 days, except like this week was only 2. Week day is Sunday to Saturday.
So with this said in a cell she enters what she did so far for the week to date sevice sales and in another her month to date service sales. 2 more cells for week to date. These figures are used to find the remaining monthly goals in the cells I want to use a formula in.
I don't know if this is any more explained tha before. Maybe I will just attach the worksheet so it might make more sense. Well in looking this over I can't seem to do that so here is a link to it on my server.
http://www.minichillosconstruction.com/chers/Weeklyreport.zip
Thank you for all the help.
Paul
 

Watch MrExcel Video

Forum statistics

Threads
1,123,366
Messages
5,601,208
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top