Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Selecting certain cells to record in another tab

    I have three different types of transactions that have to be recorded into specifiic cells in a different tab within the same workbook. all the data in in Daily_Cashiers Log 2 and to be recorded in Summary Log 2 tab.

    In the Daily Cashiers Log 2 col M has the VOIDED Receipts (highlighted pink).

    The CR items (Credit Card) has to have the Receipt #, Credit Card Amount and Credit Card Type of Service recorded in the Summary Log 2

    Lastly, REFUNDS higlighted blue needs the Receipt # from column G Daily Cashiers Log 2 and corresponding Amount from Column N.

    Without using a Macro, is there any way of populating the sections like on the Summary Log 2 with the proper receipt # and other data ( amount and type of service), if required.

    Thank you
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I don't think this can be done without a macro. I did a quirky approach, but it'll require you separately filtering each section. I did two sections to illustrate. There are array formulas in play also. It was a quick and dirty solution while my eyes were still open a bit.
    Attached Files Attached Files

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    These formulas seem to work for me to give the results you have
    [NOTE: With the exception of the SUM formula, these are all Array formulas and should be confirmed with ctrl-shift-enter]

    In 'SUMMARY LOG 2'!B11:
    Code:
    =IFERROR(INDEX('DAILY_CASHIERS LOG 2'!$G:$G,SMALL(IF(('DAILY_CASHIERS LOG 2'!$M$21:$M$194="X"),ROW('DAILY_CASHIERS LOG 2'!$M$21:$M$194)),ROW()-ROW(B$10))),"")
    Copy B11 to B12:B22


    In 'SUMMARY LOG 2'!F11:
    Code:
    =IFERROR(INDEX('DAILY_CASHIERS LOG 2'!$G:$G,SMALL(IF(('DAILY_CASHIERS LOG 2'!$N$21:$N$194>0),ROW('DAILY_CASHIERS LOG 2'!$M$21:$M$194)),ROW()-ROW(F$10))),"")
    In 'SUMMARY LOG 2'!H11:
    Code:
    =IFERROR(INDEX('DAILY_CASHIERS LOG 2'!$N:$N,SMALL(IF(('DAILY_CASHIERS LOG 2'!$N$21:$N$194>0),ROW('DAILY_CASHIERS LOG 2'!$M$21:$M$194)),ROW()-ROW(H$10))),"")
    Copy F11:H11 to F12:H21



    In 'SUMMARY LOG 2'!J10:
    Code:
    =IFERROR(INDEX('DAILY_CASHIERS LOG 2'!$G:$G,SMALL(IF(('DAILY_CASHIERS LOG 2'!$R$21:$R$194>0),ROW('DAILY_CASHIERS LOG 2'!$M$21:$M$194)),ROW()-ROW(J$9))),"")
    In 'SUMMARY LOG 2'!K10:
    Code:
    =IFERROR(INDEX('DAILY_CASHIERS LOG 2'!$R:$R,SMALL(IF(('DAILY_CASHIERS LOG 2'!$R$21:$R$194>0),ROW('DAILY_CASHIERS LOG 2'!$M$21:$M$194)),ROW()-ROW(K$9))),"")
    In 'SUMMARY LOG 2'!L10:
    Code:
    =IFERROR(INDEX('DAILY_CASHIERS LOG 2'!$L:$L,SMALL(IF(('DAILY_CASHIERS LOG 2'!$R$21:$R$194>0),ROW('DAILY_CASHIERS LOG 2'!$M$21:$M$194)),ROW()-ROW(L$9))),"")
    In 'SUMMARY LOG 2'!K20 (this is NOT an array formula)
    Code:
    =SUM(K10:K19)
    Copy J10:L10 to J11:L19

    Steve
    Last edited by sdckapr; 2013-09-23 at 07:27. Reason: Added comment about Array formulas

  4. #4
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Could you attach your worksheet please?

    Thanks

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I could but I think it makes more sense to me, to have you put in the formulas and follow the directions. I beleive it would enhance the learning experience...

    If you have particular problems with the instructions, please let me know.

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks Pittsburgh Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •