#1
|
|||
|
|||
Setting a formula parameter from an input box
I have a series of formulas in a workbook based on a Range with a fixed starting point A2 but the end of the Range can fluctuate depending on the dataset that is copied in for use. I figured I could get the Range end value from an input box but don’t know how to get this into the formulas automatically.
I’m guessing that if I was better at VBA I could probably dispense with the formulas embedded in the workbook and perform all the calculations in a VBA module. Sadly, this is beyond my level at the moment. Any advice will be very gratefully received. |
#2
|
||||
|
||||
Why do you need an input box at all? It sounds like you should be using a table (so all references will be dynamic) or at worst you could just enter the value in a cell and refer to that from the relevant formulas, or via a named range.
|
#3
|
|||
|
|||
I need it so my team can paste datasets into the workbook and the input box vale will tell the formulas (calculating mean, median, counting various values etc) what the end of the range is. Each dataset will be different.
|
#4
|
||||
|
||||
What do you mean by " end of the range" ?Only a columnar or row range ?
In this case the answer an be found at Lookup Last Value in Column or Row – Excel - Automate Excel (among others) Use the INDEX formulas which return cell references when embedded like Code:
=SUM(A2:INDEX(A:A,COUNT(A:A))) Of course posting an example sheet would make our life easier ( no pics please)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Sub vs Function to pass variable or parameter | koehlerc14 | Excel Programming | 6 | 10-19-2019 04:33 PM |
How can I pass a field as a parameter via a URL? | kelvinlewisuk1 | Outlook | 0 | 06-30-2016 08:30 AM |
Formula In Excel To Prompt User To Input Date | pclutts | Excel Programming | 4 | 08-31-2014 12:45 PM |
Can pass .expression as a parameter? | tinfanide | PowerPoint | 4 | 12-28-2012 06:14 PM |
Restricting User Input on a TextBox (and setting focus) | joatmon | Excel Programming | 1 | 06-05-2012 03:01 PM |