Expense Split Numbering Problem

[BeachRock]

Hello!

If you looked at this post before, I have rewritten this description to hopefully make more sense of the problem for which I need your help.

The attached workbook is the beginning of my attempt to create a credit card expense form in the way that my Fiscal department has requested for it to be. One of the most important things they want is the ability to manage splitting charges out to multiple accounting allocations. To keep splits grouped, my idea is to make sure that the line item numbers change for each row that a split would encompass and all line item numbers below that split would continue to the next sequential whole number. This process repeats for each split within the form.

There are 25 line items on the form. If a split starts at line item 1, it can be split 25 times. It could also be split as few as 2 times. If it is a 2-way split starting at line item 1, that line item changes to 1-1, the next line item changes to 1-2 as the second line item for the split, and the third line item number changes to 2 and all others continue to self number to 24 below.

Splits------Line Item
2-----------1-1
------------1-2
------------2
------------3
------------4
------------Etc. to 24

If in line item 3 there is another 2 way split, the line item changes from 2 to 2-1 and the next line to 2-2 and the next to 3 and all others continue to self number to 23 below.

Splits------Line Item
2-----------1-1
------------1-2
2-----------2-1
------------2-2
------------3
------------4
------------Etc. to 23

The problem is that each formula gets exponentially longer in order to accommodate for all of the possible outcomes for every line item number, making sure that FALSE is never an outcome at all. For instance, the formula I have in G10 contains 6 IF statements and 6 possible values. The formula in G11 contains 22 IF statements and has 11 possible values. Line Item 25 (G32) has 324 possible values that it could be. My brain hurts trying to imagine how many IF statements that equates to for that cell.

To be clear, I'm not looking for anyone to do this for me. I am looking for either a better way to accomplish the same thing, or any way that you know of that would shorten up the enormity of the formulas in the remaining 21 cells.

Thanks in advance for even giving a thought to this! Any help or advice you can offer will be greatly appreciated.

[BeachRock]

After much trial and error, I've been able to shorten the number of IF statements by using nested AND and OR statements.

I now have the first 5 formulas worked out and, though a pattern is starting to form, it's still difficult to work with and very slow going.

The formula from G11 went from 22 IF statements down to 9, actually one less than the number of possible values.

I'm still looking for any suggestions for shortening them even more or a completely different way that makes it much easier. If you are a true Excel Guru
and know that what I'm doing now is as good as I can hope for, please let me know and I'll close this post and continue to be driven crazy until I work
through them all.

Line item 25 will still be a minimum of 323 IF statements long... I know it's possible to do, but it's going to take forever to complete.

If you're able to help, Thanks very much!!

[BeachRock]

I have the formulas for G8 through G13 completed. Found and fixed some errors in the previous version. All 6 work as they should now.

See the attachment.

It appears that there is no other better way to do this so I'll close out my post as solved.

[BeachRock]

SO, I found out that Excel has a Nested levels of functions limit of 64. I can't continue beyond the last version I posted.

Now I need a new idea of how to accomplish the same task. Any suggestions?

[kvsrinivasamurthy]

Formula in H36, then drag across
H35=2

Please Login or Register  to view this content.

[BeachRock]

Hi kvsrinivasamurthy,

Thank you for the reply.

I'm not sure how your formula helps me do what I'm trying to do. It's definitely useful to find all of the possible values for the number provided in G35, but what I am trying to do is to have the formula within each cell, G8:G32, change their value to the next possible value for that cell based on the number of splits chose in the adjacent cells, F8:F32.

If F8 is 3, meaning they want to split that charge with 3 different accounting codes, then the value in G8 changes from 1 to 1-1, the value in F9 changes to 1-2, the value in F10 changes to 1-3, the value in F11 changes to 2 and the rest of the cells below number themselves consecutively with 3 in F12, 4 in F13, 5 in F14, etc. With 3 still being the value in F8, if 4 is placed into F12, F11 stays at 2, F12 changes from 3 to 3-1, F13 changes from 4 to 3-2, F14 changes from 5 to 3-3, F15 changes from 6 to 3-4, F16 changes from 7 to 4 and the cells below number themselves consecutively with 5 in F17, 6 in F18, 7 in F15, etc.

Tony

[kvsrinivasamurthy]

Pl see file
You can put any value in G column
Formula in G35, H35 & I35 are dragged down
Formula in J35 is dragged across.

[BeachRock]

Hi kvsrinivasamurthy,

I do appreciate your effort, but I don't think you are understanding what I'm trying to do. You are focusing on the possible values that my G8:G32 can be instead of how to get those values into G8:G32. There shouldn't need to be any formulas anywhere else except in G8:G32. These formulas should react to the numbers manually entered into F8:F32.

I made the list of all of the possible values G8:G32 could be just for the purpose of trying to accommodate for each possible value in the formulas for G8:G32.

If I enter 2 into F8, watch what happens to G8 through G11.

G8 becomes 1-1
G9 becomes 1-2 - Because of the "2 entered into F8.
G10 becomes 2
G11 becomes 3
G12 would become 4 and so on all the way down to G32, which would now be 24.

With 2 still being in F8, If I were to then enter a number, we'll say 5, into F13 then:
G8=1-1
G9=1-2
G10=2
G11=3
G12=4
G13=5-1
G14=5-2
G15=5-3
G16=5-4
G17=5-5
G18=6
G19=7 and the rest, G20:G32 self number to 8 through 20.

With F8=2 and F13=5, if I then place 3 into F24:
G8=1-1
G9=1-2
G10=2
G11=3
G12=4
G13=5-1
G14=5-2
G15=5-3
G16=5-4
G17=5-5
G18=6
G19=7
G20=8
G21=9
G22=10
G23=11
G24=12-1
G25=12-2
G26=12-3
G27=13
G28=14
G29=15
G30=16
G31=17
G32=18

Get it? No matter where you place a 2 or above into F8:F32 all of the G fields react and carry the sequence down.

Here is the 2nd part. In the above scenario with F8=2, F13=5 and F24=3, cells G9 and G14:G17 and G25:G26 should never react at all if a number is entered into F9 or F14:F17 or F25:F26 because they are already part of a split.

However, if I change F11 from nothing to 2, the values in G8:G32 should adjust themselves as follows:

G8=1-1
G9=1-2
G10=2
G11=3-1
G12=3-2
G13=4-1
G14=4-2
G15=4-3
G16=4-4
G17=4-5
G18=5
G19=6
G20=7
G21=8
G22=9
G23=10
G24=11-1
G25=11-2
G26=11-3
G27=12
G28=13
G29=14
G30=15
G31=16
G32=17

This is what I'm trying to accomplish. If you have any questions, please let me know.

Thank you very much for your help!

Tony

[BeachRock]

I've made some progress. Due to the complexity of including "-1", "-2", "-3" etc to the end of each whole number where a split is needed, I removed this entirely.

I now have a working model using whole numbers only. The formulas are way shorter for every cell and there are no nested AND or OR functions at all. However,
I still have a problem to work out that I'm hoping someone can provide their experience.

See the new attachment for the visual about the information below.

If F8 is the first cell where a split of 2 or more can be selected, all cells in G8 through the end of that split count shouldn't be affected by any number entered into F9
through the end of that split count.

For example, if F8=4, G8:G11 will be 1 and G12:G32 will increment by one. If any number is then entered into F9:F11, it shouldn't ever affect the numbering for G9:G32.
Currently it does affect them and I need them to not be affected.

If you can help with this, thank you very much. Otherwise, I'll keeping plugging away at it.

[kvsrinivasamurthy]

In G8 thendrag down

=IF(F8<>"",1,IF(ROWS(INDEX($F$8:$F8,MATCH(99^9,$F$8:$F8,1)):$F8)<=INDEX($F$8:$F8,MATCH(99^9,$F$8:$F8,1)),1,$G7+1))

[BeachRock]

Hi kvsrinivasamurthy,

Thank you for your time in creating this solution. Unfortunately, it doesn't do what I need it to do. Please see the attachment below. This is a perfectly working version of how I need it to be numbering but has a flaw which is what I need help to eliminate. I have explained this in the attachment.

I appreciate your tenacity! It's not an easy answer.

Thank you, Tony

[kvsrinivasamurthy]

Sorry for delay due to personal reasons. In G9 then drag down.

=IF(F9<>"",G8+1,IF(ROWS(INDEX($F$8:$F9,MATCH(99^9,$F$8:$F9,1)):$F9)<=INDEX($F$8:$F9,MATCH(99^9,$F$8:$F9,1)),G8,$G8+1))

[BeachRock]

Hi kvsrin,

I created a file with both mine and your formulas on the same page, each reacting to the F8:F32 Split Count entry fields. Mine still react more the way I need them to react but, I think the direction you're going with your formulas has the most potential to be capable of the correct result without being enormous formulas. I have included what I hope to be a better description of what works the way I want it to and what doesn't and what shouldn't happen. I hope it helps!

Thank you very much for all of your effort, Kvsrin. I attempted to edit your formulas but I had no luck. I don't know what the 99^9 reference means...