•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Generating a random selection of rows based on multiple criteria
Our take
Creating a powerful tool for your tabletop wargame can enhance your gameplay experience, but encountering #SPILL errors can be frustrating. This issue often arises when generating a random selection of rows based on multiple criteria. By applying filters according to your specified criteria, you can streamline the process. However, ensuring that your formulas are correctly set and that no data obstructs the output area is crucial. Additionally, exploring options to balance point values with your random selections can elevate your tool's functionality.
I am trying to create a force building tool for a tabletop wargame.
I would like it to generate multiple rows of unit info from a table based on certain criteria and have it working for the most part but keep ending up with #SPILL errors. I am sure that there is a much more efficient way of going about this.
Here's the overview:
The breakdown:
A random rom is selected from a table in the worksheet
Then a filter is applied to sort the results according to what is selected in the dropdown menu at B2
For the most part this method seems to work. The issue is that every few refreshes i get a #SPILL error
I have set this formula to run at the bottom of the sheet and ensured there was no data in any cells to the farthest right and down.
BONUS:
If there is also a way to get this to work and have the random rows be equal to a determined point value. that would be amazing.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Populate Cells From a Random Range of Cells, Based on the Contents of Different Cells.Hello Reddit pros, I am trying to create/find a formula that will do the following: Check if A2 matches values in D1:D6 and return a random value from E1:E6 into B2. The hard part is this; if A2 equals D1:D3 then the random value of B2 is from E1:E3, but if A2 equals D4:D6 then the random value of B2 is from E4:E6. For example: if A2 says Input 5, then B2 could be either Result D, Result E, or Result F. The values in the screenshot are just for demonstration; I will be able to extrapolate what I learn onto the spreadsheet I need it for. I am just curious if there is a formula to do that, or am I just having a lot of high hopes? I have tried using IF combined with INDEX and RANDBETWEEN but I cannot seem to get the formula correct for doing even the first part of what I need, let alone the second part. It looks like this: =IF(A2:A19,INDEX(D1:D3,RANDBETWEEN(1,COUNTA(E1:E3))),"") This obviously is not correct, and it returns a #VALUE error that I cannot figure out. I do not know the correct way to phrase the question to get a viable answer via internet searching, so I am once again turning to the experts on Reddit. Thanks for any insight. I am aware the formula on the screenshot is different than my post body; I deleted the top row but didn't fix the formula u/Connect_Camel_5998 solved it for me. Thanks everybody! The formula that was posted works great for what I needed! submitted by /u/ClandestineGhost [link] [comments]
- Using the #spill error as the resultI'm trying to use the filter function to generate some tables Now my one table has multiple rows that correlate to just one row in the Table I want to generate. If there is only one row that fulfills the criteria, I want the result to be one thing, and if I get a #spill error I would like it to say another thing (but not #spill). Is there any way to do this? Is there an easier way to do it? There is a column in the original data that contains the answer I want, but since it's in multiple rows I can't figure out how to not get the #spill result. I can't share any data as it is for work and English is not my native language so I'm sorry if I didn't explain properly. submitted by /u/gaensefuesschen [link] [comments]
- Formula for cascading filter optionsI'm building a quoting tool that has dynamic pricing based on options selected via drop downs. However, some (but not all) drop downs require cascading as the options available will be different depending on what's selected in an earlier drop down (in the same row). Here's the formula I'm using: =SORT(UNIQUE(FILTER(SizeKits[KIT],SizeKits[SIZE]='Other Sheet'!C13))) SIZE and KIT are named ranges. Depending on SIZE selected, it dictates the drop down options presented in KIT drop down. I've then got a lookup table referenced that has all of the options for KIT available depending on what SIZE is selected. Then I've got Data Validation to point to this formula on the "helper" sheet. This formula is on a "helper" sheet, where the quoting happens on 'Other Sheet'. The problem that I'm running into is that it's directly referencing cell C13 in Other Sheet for the other rows. There are seven rows of cells where users can select different product options, but I'm not sure how to make each row independent of each other. For example, C14 should have it's own options available depending on the other options selected in row 14. How do I make each row independent of each other? submitted by /u/Kermie88 [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]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#rows.com#natural language processing for spreadsheets#cloud-based spreadsheet applications#AI formula generation techniques#big data management in spreadsheets#conversational data analysis#Excel compatibility#real-time data collaboration#financial modeling with spreadsheets#intelligent data visualization#data visualization tools#formula generator#enterprise data management#big data performance#Excel alternatives#data analysis tools#data cleaning solutions