•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Is there a way to Goal Seek across a range of data points in Excel, instead of just a single value?
Our take
Goal Seek in Excel is a powerful tool, but it traditionally operates on a single data point, which can be limiting for users with time-series data. If you want to apply Goal Seek across multiple rows simultaneously, you may need to explore alternatives like Solver, VBA, or macros. These options allow for greater flexibility, enabling you to set one formula cell as the target while providing a range of values and corresponding cells to change.
Normally, Goal Seek requires:
- Set cell
- To value
- By changing cell
But in my case, I have time-series data and want to apply Goal Seek to many rows at once, not just a single data point.
Ideally, I would:
- Select one formula cell as the set cell
- Provide a range of target values for “To value”
- Specify the corresponding cell to change
So instead of solving for one row, it would solve for a whole range of rows.
Is there a built-in way to do this, or do I need something like Solver, VBA, or a macro to loop through the rows?
Or, if using Solver, how best to do this?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- I’ve been using Excel more lately and I’m trying to understand some of its more advanced features without making everything overly complicatedWhen working with data that has multiple conditions or needs to update automatically, what are the most efficient functions or tools to use? for example, is it better to rely on formulas like XLOOKUP and FILTER, or are there built in tools that handle this more cleanly? Also, how does excel handle performance when formulas start getting longer or more complex? Is there a point where using too many formulas slows things down significantly? What are the best built-in features in Excel for handling complex data in a simple way? submitted by /u/icepix [link] [comments]
- I need advice with navigating a large spreadsheetIf there's a beginner subreddit, let me know. Navigating seems to be a nightmare in this particular spreadsheet. Perhaps I should separate into more than one spreadsheet, and create links? I have columns all the way to IA, and 5000+ rows. Say my cursor is on AX300 and I want to get to the row that has has in the first column "Washington". Currently we search for Washington, then scroll all the way over to column AX. 12,000 times a day. There has to be a way to stay in the same column while looking for the correct row? submitted by /u/KatMagic1977 [link] [comments]
- Returning multiple matches per ID (auto-fill when dragged down?)Hi there! I have multiple assigned IDs for a single code so I need to return all matching values, not just one. I also want this to be kind of automated (?) when I drag down the upstream ID column, it can pull all corresponding values, even if they share or don't share the same code I tried using FILTER, but it only spills results in one place and doesn’t carry over properly when dragged down for other rows. I don’t want to adjust ranges every time manually since my data might get longer. Is there a way to make a formula that: works when dragged down, returns multiple matches per ID, and still updates properly even if the data goes beyond the current rows? Left Side = source workbook; Right Side = destinatioon workbook Thank you so much in advance! submitted by /u/jeankrstein [link] [comments]
- Trying to build an Excel ‘database search’ that opens another file to the right row — possible?”So I have this mini project that I wanted to do. I genuinely wanted to help my co-workers with their databases (or data banking as they say). The goal is to create an excel file wherein when they could search for a person using either an ID or name and they could click something like a link or a button and then it'll open another excel file/s and get to the exact row based on the ID and/or name. The files that'll be opened could vary so I was thinking of buttons that opens the file and highlight or locates the exact row based on the ID. I'm not sure if this could be done. I wanted to give this a try but I just don't know the term to search for. Is this doable? Any ideas how or if there are other suggestions? I'm not an expert at Excel but maybe above average. I know a little bit of VBA but I'm willing to learn. Hope someone could help to search for the right term. submitted by /u/richako [link] [comments]