Creating a Dynamic User Input Sheet

[PaulFogel123]

I have a forecasting program in Excel. The formulas to calculate future periods' forecasts depend on user-entered values on an input sheet. For example, if the user wants salaries to increase by 10% next month, he enters 10% as the forecast variable. If actual salaries this month are $10 million, next month's forecast would be $11 million. Simple so far. However, when the user then loads the next month with his actual data, it will overwrite forecast salaries for that month. Say salaries actually come in at $10.5 million, not the forecasted $11 million. I want the user-entered value on the input sheet to display 5%, the actual increase, not 10%, the forecasted increase. That's easy enough to do with an if/then statement to distinguish actual from forecast. But, if I use an if/then statement, I can't have the user enter a value into that same cell; it would overwrite the if/then statement. As this model updates itself upon each new fiscal year, each user-entered input cell needs to do double-duty.

In summary, I want the user-entered input cell to reflect either the value for a forecast period (e.g. 10%), or to calculate what that value actually was calculated upon the new uploaded values (e.g. 5%). There are two ways I've thought of to do this, both of them clunky:
1) Have two rows for each variable; one row purely for input, the other to calculate the actual result as the months advance. Displaying both of these rows might look odd; in the example above, it would show the forecast was 10% and the actual was 5%. That might not be so bad were it not for the fact that I have 200 rows of variables, and I don't want to add bulk.
2) A macro that would paste over the user-defined cell each month when actual data for that month is uploaded. But as I have 200 rows of variables with 12 months each, that would be cumbersome.

Anyone have some creative ideas?

 

[Hui]

I'd go with Option 1 purely from the point of view that it gives you a record of how your forecasts compare to actuals and will be simpler

 

[SirJB7]

Hi, PaulFogel123!

I'd go with Option 2 since I wouldn't like to deal with such a bunch of data via formulas, if what I only have to do could be done in the change event of worksheet class module.

However, if you choose the 2nd too, consider uploading (a) sample file(s) (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you, relieving the contributor of having to build a test file, if necessary. Thank you.

Regards!

 

[PaulFogel123]

Hi, PaulFogel123!

I'd go with Option 2 since I wouldn't like to deal with such a bunch of data via formulas, if what I only have to do could be done in the change event of worksheet class module.

However, if you choose the 2nd too, consider uploading (a) sample file(s) (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you, relieving the contributor of having to build a test file, if necessary. Thank you.

Regards!

Hello Sir JB7,
What is meant by "change event of worksheet class module?"
Since it appears that a cell can't both allow for user input and contain a formula (the formula would be overwritten when the entered a number), Option 1 might be the only viable choice. I was hoping that there'd be a way to have a form pop up, perhaps, that would allow a user to input if it was in a future period, but disallow if in a current period, while still preserving an if/then formula.

 

[SirJB7]

Hi, PaulFogel123!

Sorry for the late answer but been traveling the last couple of weeks.

Your goal may be simply achieved with a macro rather than with a lot of formulas. This macro can be manually run from the macro menu (Alt-F8), from an assigned shortcut (Ctrl-xxx), by clicking on a command button, or automatically too trapping the events that occur on a worksheet, in this case the change of a cell value by inputting a new value. There are specific procedure names (Private Sub Worksheet_Change) that are stored in the worksheet class module (that's the area code of the related worksheet, try Alt-F11 and then double click on VBA Project and then in the worksheet name).

As I wrote you earlier, consider uploading a sample file to get a proposed solution.

Regards!

 

[bobhc]

Sorry for the late answer but been traveling the last couple of weeks

Like most people SirJB7 has a 48 week work year, .........but in his case he works 8 weeks and holidays 40 weeks....



.

 

[SirJB7]

@b(ut)ob(ut)hc
Hi, my dear and very old friend!
Good afternoon.
May I correct you, if you allow me? At least in my third-world country people start with 2 vacation weeks per year, getting one additional week for each 5 years at the same job. So either in your first-world country people start with the double or the dominant specie at your environment is the dinosaur...
Regards!
PS: BTW, I'd swear I said traveling, not enjoying holidays. Here's my binnacle:
Easter holidays on April 17-20, job travel abroad on April 21-25 + weekend, labor day on May 1st extended to 2nd in this country (yes, tell our politicians that they shouldn't), so why working on April 28-30?, and then a required relax week on May 4-11... and coming back this week.
So 50-50, deal?

 

[bobhc]

Let me shake the sand from my feet

sand on the feet = beach......Long live the dinosaurs....

 

[PaulFogel123]

Hi, PaulFogel123!

Sorry for the late answer but been traveling the last couple of weeks.

Your goal may be simply achieved with a macro rather than with a lot of formulas. This macro can be manually run from the macro menu (Alt-F8), from an assigned shortcut (Ctrl-xxx), by clicking on a command button, or automatically too trapping the events that occur on a worksheet, in this case the change of a cell value by inputting a new value. There are specific procedure names (Private Sub Worksheet_Change) that are stored in the worksheet class module (that's the area code of the related worksheet, try Alt-F11 and then double click on VBA Project and then in the worksheet name).

As I wrote you earlier, consider uploading a sample file to get a proposed solution.

Regards!

Sorry for my equally late reply back. I agree with your solution, but advanced VBA is beyond me. I can, however, record macros with the Record Macro button, and that has worked out okay so far. My general rule is that a macro can do what a formula can't.

Here's an example of my file:

Cell BU10 shows 3.4%, a user-entered value. This is the amount the user is forecasting Medicare rates to increase. However, if Jan-2014 has already occurred, I want that cell to display the actual rate increase from Dec-2013, which I can calculate elsewhere and display here. An IF statement would work, but it would require the user to edit the cell, and I don't want that. I've experimented with Forms and whatnot, but it seems the only way to do this is with a macro. When Jan 2014 closes out, a macro would enter a calculation formula in BU10.

Hi, PaulFogel123!

Sorry for the late answer but been traveling the last couple of weeks.

Your goal may be simply achieved with a macro rather than with a lot of formulas. This macro can be manually run from the macro menu (Alt-F8), from an assigned shortcut (Ctrl-xxx), by clicking on a command button, or automatically too trapping the events that occur on a worksheet, in this case the change of a cell value by inputting a new value. There are specific procedure names (Private Sub Worksheet_Change) that are stored in the worksheet class module (that's the area code of the related worksheet, try Alt-F11 and then double click on VBA Project and then in the worksheet name).

As I wrote you earlier, consider uploading a sample file to get a proposed solution.

Regards!