Ultimate Challenge: Generate List from Checkboxes &/or Values

**nocode1437** · 10-09-2012, 05:08 PM

Relatively new to Excel, but I'm confident that this is possible to do:

I have a Construction Budget that I want to subsequently produce a Payment Schedule in phases.
I want to be able to check boxes on line items on my Budget tab (according to their phase, including multiple phase selections) that will magically generate a new list for each phase on the Payment tab.
For line items that have "x" in multiple phases, the output should divide the $ evenly between the multiple phases.
I also want to be able to specify a specific percentage in the box instead of an "x" (i.e. "50, x, x" -this would give 50% to the phase with 50 and then divide the remaining total evenly).
The list needs to maintain its numerical order whether boxes are checked or unchecked.

If that wasn't enough, the lists need to be able to change dynamically, as I check or uncheck boxes, but it still needs to provide a subtotal below the last line.

The phases could be displayed on individual tabs.

If you are up to the challenge, I would love your help.
Thank you for your time, and genius.
-Tim

I've included a sample of what it would possibly look like if the Budget page actually did link to the Payment Schedule tab as I dream. I calculated out the first three imaginary phases to hopefully help anyone understand exactly what I'm trying to do.

**protonLeah** · 10-09-2012, 06:21 PM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

-------------------
This is a pretty big request in one chunk. I'm sure you'll get better response if you break down your question into several parts, so that they can be tackled one after the other.

Also, we're happy to help you with your effort, but nobody here will be keen on developing a solution from scratch. This forum is about helping people learn how to use Excel, not a free development service.

Have you started any work on this project yet?
How far did you get?
Where are you stuck?

We can help you develop your own code, but if you want someone to code this for you all the way, you should try and find a commercial developer.

**nocode1437** · 10-09-2012, 11:57 PM

Excuse my title... If "ultimate challenge" offends, I will happily edit it... Nevertheless, I tried to describe my issue concisely and directly per rule 1. Please advise if necessary on the titular changes to meet forum compliance. I only want help, not to entangle in bureaucracy (which I appreciate being a necessity as a forum mod myself) so I'm open to any necessary changes... Don't hesitate to enlighten me along the way in my first thread.

I did not include my initial struggles because I believe there is a better more efficient way to proceed and I don't want to hinder the creative thinking involved...
That said, I started with a conditional that first COUNTIF there are any "x" then if yes index the row of text where the x is. But I have trouble combing "x" and a value like 50 being recognized together as an indicator for indexing the row. Trying to move past that stage, I concentrated on also tallying the "x" and using the count to divide the initial value, but my trouble appeared when any changes to the dynamic indexed list throws off the referenced values. Calculating percentages and dividing the remained by the x count and displaying it accordingly was a senseless endeavor given that I couldn't even associate the calculations with the ever changing list indexed by the checkboxes.

My code is trash, but if you think it would help I will gladly post the failing strings.
If the sample file I posted was not clear enough about the problems I'm trying to overcome, I'm happy to elaborate. I know excel can do this, and I'm sure with our powers combined we can figure this out.
Again, thank you for your time and creative brilliance.

**Melvinrobb** · 10-10-2012, 10:28 AM

I used helper columns in Y-AF to get the appropriate $ for each Phase.
I then used Vlookup to get the numbers into the payment sheet.
It isn't perfect user friendly yet, but the concept is there for you to work with and make ideal for your purposes.

Note: in order for VLOOKUP to work, you need to change each line to unique values. I change the "Construction" Header to "Construction Fees" so the vlookup did not look to that line for the construction numbers.

**nocode1437** · 10-10-2012, 01:41 PM

Thank you very much Melvinrobb. I think this might be a really helpful start towards my end goal. I'll see what I can work out of this information to generate my final vision and post back with an update.
Again, thank you... your method very effectively accomplishes recognizing % and x values and divides the values according!
-Tim

I was able to utilize the VLOOKUP for the time being, but my end goal would be to index the payment list instantly upon placing a X in the box, or value... Still hacking away at it... any new ideas are more than welcome. THANKS!

**nocode1437** · 10-11-2012, 03:06 PM

it took me a day or so, but I finally got this little guy to work... at least, well enough for now.

here's the code i used instead of the VLOOKUP:
(since vlookup wouldn't generate the list automatically from the actual check box)
(I made another helper array consolidating the x's and #%'s into a simple x or "" output for the indexing)

{=IFERROR(IF(COUNTIF(Budget!$AA$9:$AA$60,"x")>=ROWS(Budget!$O$9:$AA$9),INDEX(Budget!C$9:C$60,SMALL(IF(Budget!$AA$9:$AA$60="x",ROW(INDIRECT("1:"&ROWS(Budget!$U$9:$U$60)))),ROW(B1))),""),"")}

If you see any simplification, i'm all ears!
i've come across a new problem, but I'll make a separate post for that one.

Thanks again Melvin, you really got the ball rolling on this one. I really appreciate it.
-Tim