Thread: Function where results cell equals partial "text" of reference cell??? HELP!!!

Ummmm...all I can say is WOW!!!!!!!

Thank you VERY VERY VERY Much!!!!!!!!

DILIPandey,

Ok, I went in and apparently I hadn't copied all the drop down boxes in the first sheet, so they didn't copy to your formula. See attached to see what I mean.

Sample Excel.xlsx

Originally Posted by dilipandey

Hi Larry,

I am unable to see my formula into the spreadsheet... have you applied that or do you want me to apply and send back?

Regards,
DILIPandey

<click on below 'star' if this helps>

If I deleted it I didn't mean to, if you could apply back I would appreciate it! Thank you again!!!

Thank you NBVC!!! This worked perfect. Appreciate your timely help!!!

Originally Posted by NBVC

In K3, try:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",LEFT(INDEX($H$8:$H$34,MATCH(TRUE,INDEX(ISNA(MATCH(LEFT($H$8:$H $34,4),$K$2:$K2,0)),0),0)),4),""))

copied down

in L3:

=IF(K3="","",SUMIF($H$8:$I$34,K3&"*",$J$8:$J$34))

copied down

Didn't work after all...here's where I am.

Demo Excel Sheet.xlsx

Ok!! I believe I am close. I have tried everyone's formulas/functions and it seems to be close when inserted to a particular cell, but when I try to create what will be input and used on a normal basis, nothing pulls appropriately to K, L. When I plug in the formula in one cell, it might work there, but when I copy down, it does not.

Here is exactly what I am trying to accomplish. Employee will enter the description of an expense in column "A". In column "B" the date and in "E" the respective amount. Easy enough. Now what I am trying to do is allow the employee to use the dropdowns in "H" to correctly classify the respective expense. In column "J", should just match the total in "E".

The tricky part is now getting the 4 digit portion of the Account Code's that are used in "H" only to be reflected in column "J", BUT only once, if used, and consolidated for the number of entries or itemized expenses.

EXAMPLE: if I enter "Gas" in "A" and 02/08/12 for the date in "B" and $53.00 for "E" for the amount. I use the dropdown and select, "6502-Auto & Truck Expense-Fuel, Gas & Propane-Cost of Goods Sold". Column "J" should also reflect $53.00.

I need column "K" to reflect "6502" (only) and $53.00 to appear in Column "L". Now if I enter Office Supplies in "A", 02/08/12 in "B" and $32.00 in "E". I use dropdown and select "7310-Office Equipment/Supplies-Consumable Supplies-SG&A Expense" - Column "J" should read $32.00. Column "K" should show "7310" and "L" should show $32.00.

If I enter another subsequent "Gas" or 6502 or "Office Supplies" or 7310 expense in other entries (for example another "Gas" entry for $51.00), Column "K" should still only have one '6502' cell and "L" should now read "104.00".

If there also anyway for columns E, J & L to be blank without a prefilled "$" or a "0" until an entry is made, would be an added bonus!!!!!!!!

This is the best way I can explain it perhaps. I have attached another 'cleared field' spreadsheet. Can anyone just input these formulas that clearly seem to work for you (and me) until I try to copy and paste down. I can't tell you how much I would appreciate it!!!! Please let me know if there are any questions. I thought I was somewhat saavy enough to do this, but have proven my shortcomings in detail!!!!

Demo Excel Sheet Test.xlsx