Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-07-2022, 11:22 AM
CuriousG CuriousG is offline Setting a formula parameter from an input box Windows 10 Setting a formula parameter from an input box Office 2016
Novice
Setting a formula parameter from an input box
 
Join Date: Jan 2021
Posts: 20
CuriousG is on a distinguished road
Default 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 dont know how to get this into the formulas automatically.

Im 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.
Reply With Quote
  #2  
Old 09-12-2022, 01:53 AM
Debaser's Avatar
Debaser Debaser is offline Setting a formula parameter from an input box Windows 7 64bit Setting a formula parameter from an input box Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 197
Debaser will become famous soon enough
Default

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.
Reply With Quote
  #3  
Old 09-15-2022, 04:12 AM
CuriousG CuriousG is offline Setting a formula parameter from an input box Windows 10 Setting a formula parameter from an input box Office 2016
Novice
Setting a formula parameter from an input box
 
Join Date: Jan 2021
Posts: 20
CuriousG is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 09-17-2022, 06:01 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Setting a formula parameter from an input box Windows 10 Setting a formula parameter from an input box Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,601
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

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)))
or variations thereupon
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
Reply With Quote
Reply

Thread Tools
Display Modes


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
Setting a formula parameter from an input box Formula In Excel To Prompt User To Input Date pclutts Excel Programming 4 08-31-2014 12:45 PM
Setting a formula parameter from an input box Can pass .expression as a parameter? tinfanide PowerPoint 4 12-28-2012 06:14 PM
Setting a formula parameter from an input box Restricting User Input on a TextBox (and setting focus) joatmon Excel Programming 1 06-05-2012 03:01 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:23 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft