Can I request user in input variable value within formula?
I don't even know if this can be done, but I would like to request the user
to define the value of a variable within a formula without having to reference a separate data table or cell Example imaginary formula: A3 =(USER DEFINED VALUE) * A1 Such that the user inputs the value direction into the cell but the shown value is the fuction result. |
Can I request user in input variable value within formula?
Try this small macro:
Sub uservalue() x = Application.InputBox(prompt:="enter the value", Type:=1) Range("A3").Formula = "=" & x & "*A1" End Sub -- Gary''s Student - gsnu200789 "R Nelson" wrote: I don't even know if this can be done, but I would like to request the user to define the value of a variable within a formula without having to reference a separate data table or cell Example imaginary formula: A3 =(USER DEFINED VALUE) * A1 Such that the user inputs the value direction into the cell but the shown value is the fuction result. |
Can I request user in input variable value within formula?
Thanks, that is exactly what I was looking for!!!
"Gary''s Student" wrote: Try this small macro: Sub uservalue() x = Application.InputBox(prompt:="enter the value", Type:=1) Range("A3").Formula = "=" & x & "*A1" End Sub -- Gary''s Student - gsnu200789 "R Nelson" wrote: I don't even know if this can be done, but I would like to request the user to define the value of a variable within a formula without having to reference a separate data table or cell Example imaginary formula: A3 =(USER DEFINED VALUE) * A1 Such that the user inputs the value direction into the cell but the shown value is the fuction result. |
Can I request user in input variable value within formula?
Okay, that didn't work like I first thought. The sub works great, but let me
better explain what I would like to do.. I have a dollar value in A1, and I would like to be able to let the user spread that value throughout cells B1:H1 (across the row) by percentage. So it would look like this if the user input 25% in each cell. A1 B1 C1 D1 E1 100,000 25000 25000 25000 25000 Also, I am doing this same activity with values all down column A (with different spread values throughout. And...I need to be able to have the macro run when the user clicks on each cell. Is this possible? "Gary''s Student" wrote: Try this small macro: Sub uservalue() x = Application.InputBox(prompt:="enter the value", Type:=1) Range("A3").Formula = "=" & x & "*A1" End Sub -- Gary''s Student - gsnu200789 "R Nelson" wrote: I don't even know if this can be done, but I would like to request the user to define the value of a variable within a formula without having to reference a separate data table or cell Example imaginary formula: A3 =(USER DEFINED VALUE) * A1 Such that the user inputs the value direction into the cell but the shown value is the fuction result. |
Can I request user in input variable value within formula?
If the user inputs 100% we will only fill B1.
If the user inputs 50% we will fill B1 & C1. If the user inputs 33% we will fill B1 & C1 & D1. What if the user inputs 60% ??? -- Gary''s Student - gsnu200789 "R Nelson" wrote: Okay, that didn't work like I first thought. The sub works great, but let me better explain what I would like to do.. I have a dollar value in A1, and I would like to be able to let the user spread that value throughout cells B1:H1 (across the row) by percentage. So it would look like this if the user input 25% in each cell. A1 B1 C1 D1 E1 100,000 25000 25000 25000 25000 Also, I am doing this same activity with values all down column A (with different spread values throughout. And...I need to be able to have the macro run when the user clicks on each cell. Is this possible? "Gary''s Student" wrote: Try this small macro: Sub uservalue() x = Application.InputBox(prompt:="enter the value", Type:=1) Range("A3").Formula = "=" & x & "*A1" End Sub -- Gary''s Student - gsnu200789 "R Nelson" wrote: I don't even know if this can be done, but I would like to request the user to define the value of a variable within a formula without having to reference a separate data table or cell Example imaginary formula: A3 =(USER DEFINED VALUE) * A1 Such that the user inputs the value direction into the cell but the shown value is the fuction result. |
Can I request user in input variable value within formula?
That is exactly my problem. B1:E1 are dates, so we are spreading that value
across dates, but not necessary all of them. It could be 60% in C1, 40% in E1 and 0% in B1 and D1. Basically I'm doing a cash flow spread, which I typically have to do manually for payment terms of each pay item, but if I can get this idea working, then it expedites the cost spread process and allows other users to adjust easily in the future. "Gary''s Student" wrote: If the user inputs 100% we will only fill B1. If the user inputs 50% we will fill B1 & C1. If the user inputs 33% we will fill B1 & C1 & D1. What if the user inputs 60% ??? -- Gary''s Student - gsnu200789 "R Nelson" wrote: Okay, that didn't work like I first thought. The sub works great, but let me better explain what I would like to do.. I have a dollar value in A1, and I would like to be able to let the user spread that value throughout cells B1:H1 (across the row) by percentage. So it would look like this if the user input 25% in each cell. A1 B1 C1 D1 E1 100,000 25000 25000 25000 25000 Also, I am doing this same activity with values all down column A (with different spread values throughout. And...I need to be able to have the macro run when the user clicks on each cell. Is this possible? "Gary''s Student" wrote: Try this small macro: Sub uservalue() x = Application.InputBox(prompt:="enter the value", Type:=1) Range("A3").Formula = "=" & x & "*A1" End Sub -- Gary''s Student - gsnu200789 "R Nelson" wrote: I don't even know if this can be done, but I would like to request the user to define the value of a variable within a formula without having to reference a separate data table or cell Example imaginary formula: A3 =(USER DEFINED VALUE) * A1 Such that the user inputs the value direction into the cell but the shown value is the fuction result. |
Can I request user in input variable value within formula?
Now I get it!
Check back tomorrow....We will let the user double-click cols B thru E (or B thru anything). The user will supply the % and the cell will receive that percentage of the col A value. For example, if the user is on row 13 and double-clicks D13, they will input a percentage ( say 13.538%) and D13 will get the formula: =.13538*A1 REMEMBER: You STILL have to be concerned with the user picking a set of percentages that do not add up to 100% -- Gary''s Student - gsnu200789 "R Nelson" wrote: That is exactly my problem. B1:E1 are dates, so we are spreading that value across dates, but not necessary all of them. It could be 60% in C1, 40% in E1 and 0% in B1 and D1. Basically I'm doing a cash flow spread, which I typically have to do manually for payment terms of each pay item, but if I can get this idea working, then it expedites the cost spread process and allows other users to adjust easily in the future. "Gary''s Student" wrote: If the user inputs 100% we will only fill B1. If the user inputs 50% we will fill B1 & C1. If the user inputs 33% we will fill B1 & C1 & D1. What if the user inputs 60% ??? -- Gary''s Student - gsnu200789 "R Nelson" wrote: Okay, that didn't work like I first thought. The sub works great, but let me better explain what I would like to do.. I have a dollar value in A1, and I would like to be able to let the user spread that value throughout cells B1:H1 (across the row) by percentage. So it would look like this if the user input 25% in each cell. A1 B1 C1 D1 E1 100,000 25000 25000 25000 25000 Also, I am doing this same activity with values all down column A (with different spread values throughout. And...I need to be able to have the macro run when the user clicks on each cell. Is this possible? "Gary''s Student" wrote: Try this small macro: Sub uservalue() x = Application.InputBox(prompt:="enter the value", Type:=1) Range("A3").Formula = "=" & x & "*A1" End Sub -- Gary''s Student - gsnu200789 "R Nelson" wrote: I don't even know if this can be done, but I would like to request the user to define the value of a variable within a formula without having to reference a separate data table or cell Example imaginary formula: A3 =(USER DEFINED VALUE) * A1 Such that the user inputs the value direction into the cell but the shown value is the fuction result. |
Can I request user in input variable value within formula?
Awesome!
Yes, if I can build in the ability to spread these values across the row (A1 to B1:Z1, A2 to B2:Z2, etc..) that will be excellent! As for adding up to 100% of the value, I'll just have a sum column in there as a check. If the user in question doesn't know to distribute the full 100% then he has bigger problems than the spreadsheet! :) Thanks so much for this help. "Gary''s Student" wrote: Now I get it! Check back tomorrow....We will let the user double-click cols B thru E (or B thru anything). The user will supply the % and the cell will receive that percentage of the col A value. For example, if the user is on row 13 and double-clicks D13, they will input a percentage ( say 13.538%) and D13 will get the formula: =.13538*A1 REMEMBER: You STILL have to be concerned with the user picking a set of percentages that do not add up to 100% -- Gary''s Student - gsnu200789 "R Nelson" wrote: That is exactly my problem. B1:E1 are dates, so we are spreading that value across dates, but not necessary all of them. It could be 60% in C1, 40% in E1 and 0% in B1 and D1. Basically I'm doing a cash flow spread, which I typically have to do manually for payment terms of each pay item, but if I can get this idea working, then it expedites the cost spread process and allows other users to adjust easily in the future. "Gary''s Student" wrote: If the user inputs 100% we will only fill B1. If the user inputs 50% we will fill B1 & C1. If the user inputs 33% we will fill B1 & C1 & D1. What if the user inputs 60% ??? -- Gary''s Student - gsnu200789 "R Nelson" wrote: Okay, that didn't work like I first thought. The sub works great, but let me better explain what I would like to do.. I have a dollar value in A1, and I would like to be able to let the user spread that value throughout cells B1:H1 (across the row) by percentage. So it would look like this if the user input 25% in each cell. A1 B1 C1 D1 E1 100,000 25000 25000 25000 25000 Also, I am doing this same activity with values all down column A (with different spread values throughout. And...I need to be able to have the macro run when the user clicks on each cell. Is this possible? "Gary''s Student" wrote: Try this small macro: Sub uservalue() x = Application.InputBox(prompt:="enter the value", Type:=1) Range("A3").Formula = "=" & x & "*A1" End Sub -- Gary''s Student - gsnu200789 "R Nelson" wrote: I don't even know if this can be done, but I would like to request the user to define the value of a variable within a formula without having to reference a separate data table or cell Example imaginary formula: A3 =(USER DEFINED VALUE) * A1 Such that the user inputs the value direction into the cell but the shown value is the fuction result. |
Can I request user in input variable value within formula?
Here is the macro. Double-click anywhere from B1 thru Z100 and the macro
runs. It asks for a percentage, so have the user enter stuff like 45%. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Set r = Range("B1:Z100") If Intersect(Target, r) Is Nothing Then Exit Sub Cancel = True rr = Target.Row x = Application.InputBox(prompt:="Enter a percentage (like 15%)", Type:=2) Target.Formula = "=A" & rr & "*" & x End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200789 "R Nelson" wrote: Awesome! Yes, if I can build in the ability to spread these values across the row (A1 to B1:Z1, A2 to B2:Z2, etc..) that will be excellent! As for adding up to 100% of the value, I'll just have a sum column in there as a check. If the user in question doesn't know to distribute the full 100% then he has bigger problems than the spreadsheet! :) Thanks so much for this help. "Gary''s Student" wrote: Now I get it! Check back tomorrow....We will let the user double-click cols B thru E (or B thru anything). The user will supply the % and the cell will receive that percentage of the col A value. For example, if the user is on row 13 and double-clicks D13, they will input a percentage ( say 13.538%) and D13 will get the formula: =.13538*A1 REMEMBER: You STILL have to be concerned with the user picking a set of percentages that do not add up to 100% -- Gary''s Student - gsnu200789 "R Nelson" wrote: That is exactly my problem. B1:E1 are dates, so we are spreading that value across dates, but not necessary all of them. It could be 60% in C1, 40% in E1 and 0% in B1 and D1. Basically I'm doing a cash flow spread, which I typically have to do manually for payment terms of each pay item, but if I can get this idea working, then it expedites the cost spread process and allows other users to adjust easily in the future. "Gary''s Student" wrote: If the user inputs 100% we will only fill B1. If the user inputs 50% we will fill B1 & C1. If the user inputs 33% we will fill B1 & C1 & D1. What if the user inputs 60% ??? -- Gary''s Student - gsnu200789 "R Nelson" wrote: Okay, that didn't work like I first thought. The sub works great, but let me better explain what I would like to do.. I have a dollar value in A1, and I would like to be able to let the user spread that value throughout cells B1:H1 (across the row) by percentage. So it would look like this if the user input 25% in each cell. A1 B1 C1 D1 E1 100,000 25000 25000 25000 25000 Also, I am doing this same activity with values all down column A (with different spread values throughout. And...I need to be able to have the macro run when the user clicks on each cell. Is this possible? "Gary''s Student" wrote: Try this small macro: Sub uservalue() x = Application.InputBox(prompt:="enter the value", Type:=1) Range("A3").Formula = "=" & x & "*A1" End Sub -- Gary''s Student - gsnu200789 "R Nelson" wrote: I don't even know if this can be done, but I would like to request the user to define the value of a variable within a formula without having to reference a separate data table or cell Example imaginary formula: A3 =(USER DEFINED VALUE) * A1 Such that the user inputs the value direction into the cell but the shown value is the fuction result. |
Can I request user in input variable value within formula?
Well, I plugged it in to a tester worksheet as a practice run and I cannot
get it to work. Code is pasted into worksheet as a worksheet event (thanks for the link to worksheet events tutorial by the way) but no action on double click. I'll keep working with it to see if I can figure it out, but I don't know what the problem could be. "Gary''s Student" wrote: Here is the macro. Double-click anywhere from B1 thru Z100 and the macro runs. It asks for a percentage, so have the user enter stuff like 45%. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Set r = Range("B1:Z100") If Intersect(Target, r) Is Nothing Then Exit Sub Cancel = True rr = Target.Row x = Application.InputBox(prompt:="Enter a percentage (like 15%)", Type:=2) Target.Formula = "=A" & rr & "*" & x End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200789 "R Nelson" wrote: Awesome! Yes, if I can build in the ability to spread these values across the row (A1 to B1:Z1, A2 to B2:Z2, etc..) that will be excellent! As for adding up to 100% of the value, I'll just have a sum column in there as a check. If the user in question doesn't know to distribute the full 100% then he has bigger problems than the spreadsheet! :) Thanks so much for this help. "Gary''s Student" wrote: Now I get it! Check back tomorrow....We will let the user double-click cols B thru E (or B thru anything). The user will supply the % and the cell will receive that percentage of the col A value. For example, if the user is on row 13 and double-clicks D13, they will input a percentage ( say 13.538%) and D13 will get the formula: =.13538*A1 REMEMBER: You STILL have to be concerned with the user picking a set of percentages that do not add up to 100% -- Gary''s Student - gsnu200789 "R Nelson" wrote: That is exactly my problem. B1:E1 are dates, so we are spreading that value across dates, but not necessary all of them. It could be 60% in C1, 40% in E1 and 0% in B1 and D1. Basically I'm doing a cash flow spread, which I typically have to do manually for payment terms of each pay item, but if I can get this idea working, then it expedites the cost spread process and allows other users to adjust easily in the future. "Gary''s Student" wrote: If the user inputs 100% we will only fill B1. If the user inputs 50% we will fill B1 & C1. If the user inputs 33% we will fill B1 & C1 & D1. What if the user inputs 60% ??? -- Gary''s Student - gsnu200789 "R Nelson" wrote: Okay, that didn't work like I first thought. The sub works great, but let me better explain what I would like to do.. I have a dollar value in A1, and I would like to be able to let the user spread that value throughout cells B1:H1 (across the row) by percentage. So it would look like this if the user input 25% in each cell. A1 B1 C1 D1 E1 100,000 25000 25000 25000 25000 Also, I am doing this same activity with values all down column A (with different spread values throughout. And...I need to be able to have the macro run when the user clicks on each cell. Is this possible? "Gary''s Student" wrote: Try this small macro: Sub uservalue() x = Application.InputBox(prompt:="enter the value", Type:=1) Range("A3").Formula = "=" & x & "*A1" End Sub -- Gary''s Student - gsnu200789 "R Nelson" wrote: I don't even know if this can be done, but I would like to request the user to define the value of a variable within a formula without having to reference a separate data table or cell Example imaginary formula: A3 =(USER DEFINED VALUE) * A1 Such that the user inputs the value direction into the cell but the shown value is the fuction result. |
Can I request user in input variable value within formula?
I got it working. Simply an error on my part in transposing to the worksheet.
Thanks again very much!!! "Gary''s Student" wrote: Here is the macro. Double-click anywhere from B1 thru Z100 and the macro runs. It asks for a percentage, so have the user enter stuff like 45%. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Set r = Range("B1:Z100") If Intersect(Target, r) Is Nothing Then Exit Sub Cancel = True rr = Target.Row x = Application.InputBox(prompt:="Enter a percentage (like 15%)", Type:=2) Target.Formula = "=A" & rr & "*" & x End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200789 "R Nelson" wrote: Awesome! Yes, if I can build in the ability to spread these values across the row (A1 to B1:Z1, A2 to B2:Z2, etc..) that will be excellent! As for adding up to 100% of the value, I'll just have a sum column in there as a check. If the user in question doesn't know to distribute the full 100% then he has bigger problems than the spreadsheet! :) Thanks so much for this help. "Gary''s Student" wrote: Now I get it! Check back tomorrow....We will let the user double-click cols B thru E (or B thru anything). The user will supply the % and the cell will receive that percentage of the col A value. For example, if the user is on row 13 and double-clicks D13, they will input a percentage ( say 13.538%) and D13 will get the formula: =.13538*A1 REMEMBER: You STILL have to be concerned with the user picking a set of percentages that do not add up to 100% -- Gary''s Student - gsnu200789 "R Nelson" wrote: That is exactly my problem. B1:E1 are dates, so we are spreading that value across dates, but not necessary all of them. It could be 60% in C1, 40% in E1 and 0% in B1 and D1. Basically I'm doing a cash flow spread, which I typically have to do manually for payment terms of each pay item, but if I can get this idea working, then it expedites the cost spread process and allows other users to adjust easily in the future. "Gary''s Student" wrote: If the user inputs 100% we will only fill B1. If the user inputs 50% we will fill B1 & C1. If the user inputs 33% we will fill B1 & C1 & D1. What if the user inputs 60% ??? -- Gary''s Student - gsnu200789 "R Nelson" wrote: Okay, that didn't work like I first thought. The sub works great, but let me better explain what I would like to do.. I have a dollar value in A1, and I would like to be able to let the user spread that value throughout cells B1:H1 (across the row) by percentage. So it would look like this if the user input 25% in each cell. A1 B1 C1 D1 E1 100,000 25000 25000 25000 25000 Also, I am doing this same activity with values all down column A (with different spread values throughout. And...I need to be able to have the macro run when the user clicks on each cell. Is this possible? "Gary''s Student" wrote: Try this small macro: Sub uservalue() x = Application.InputBox(prompt:="enter the value", Type:=1) Range("A3").Formula = "=" & x & "*A1" End Sub -- Gary''s Student - gsnu200789 "R Nelson" wrote: I don't even know if this can be done, but I would like to request the user to define the value of a variable within a formula without having to reference a separate data table or cell Example imaginary formula: A3 =(USER DEFINED VALUE) * A1 Such that the user inputs the value direction into the cell but the shown value is the fuction result. |
Can I request user in input variable value within formula?
Update this post thread if you need any moe help.
-- Gary''s Student - gsnu200789 "R Nelson" wrote: I got it working. Simply an error on my part in transposing to the worksheet. Thanks again very much!!! "Gary''s Student" wrote: Here is the macro. Double-click anywhere from B1 thru Z100 and the macro runs. It asks for a percentage, so have the user enter stuff like 45%. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Set r = Range("B1:Z100") If Intersect(Target, r) Is Nothing Then Exit Sub Cancel = True rr = Target.Row x = Application.InputBox(prompt:="Enter a percentage (like 15%)", Type:=2) Target.Formula = "=A" & rr & "*" & x End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200789 "R Nelson" wrote: Awesome! Yes, if I can build in the ability to spread these values across the row (A1 to B1:Z1, A2 to B2:Z2, etc..) that will be excellent! As for adding up to 100% of the value, I'll just have a sum column in there as a check. If the user in question doesn't know to distribute the full 100% then he has bigger problems than the spreadsheet! :) Thanks so much for this help. "Gary''s Student" wrote: Now I get it! Check back tomorrow....We will let the user double-click cols B thru E (or B thru anything). The user will supply the % and the cell will receive that percentage of the col A value. For example, if the user is on row 13 and double-clicks D13, they will input a percentage ( say 13.538%) and D13 will get the formula: =.13538*A1 REMEMBER: You STILL have to be concerned with the user picking a set of percentages that do not add up to 100% -- Gary''s Student - gsnu200789 "R Nelson" wrote: That is exactly my problem. B1:E1 are dates, so we are spreading that value across dates, but not necessary all of them. It could be 60% in C1, 40% in E1 and 0% in B1 and D1. Basically I'm doing a cash flow spread, which I typically have to do manually for payment terms of each pay item, but if I can get this idea working, then it expedites the cost spread process and allows other users to adjust easily in the future. "Gary''s Student" wrote: If the user inputs 100% we will only fill B1. If the user inputs 50% we will fill B1 & C1. If the user inputs 33% we will fill B1 & C1 & D1. What if the user inputs 60% ??? -- Gary''s Student - gsnu200789 "R Nelson" wrote: Okay, that didn't work like I first thought. The sub works great, but let me better explain what I would like to do.. I have a dollar value in A1, and I would like to be able to let the user spread that value throughout cells B1:H1 (across the row) by percentage. So it would look like this if the user input 25% in each cell. A1 B1 C1 D1 E1 100,000 25000 25000 25000 25000 Also, I am doing this same activity with values all down column A (with different spread values throughout. And...I need to be able to have the macro run when the user clicks on each cell. Is this possible? "Gary''s Student" wrote: Try this small macro: Sub uservalue() x = Application.InputBox(prompt:="enter the value", Type:=1) Range("A3").Formula = "=" & x & "*A1" End Sub -- Gary''s Student - gsnu200789 "R Nelson" wrote: I don't even know if this can be done, but I would like to request the user to define the value of a variable within a formula without having to reference a separate data table or cell Example imaginary formula: A3 =(USER DEFINED VALUE) * A1 Such that the user inputs the value direction into the cell but the shown value is the fuction result. |
All times are GMT +1. The time now is 02:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com