+ Reply to Thread
Results 1 to 3 of 3

INDEX MATCH query to include BLANK ENTRIES from source, so WEEK NUMBER obvious at a glance

  1. #1
    Registered User
    Join Date
    07-26-2010
    Location
    Hull, England
    MS-Off Ver
    Excel 2003
    Posts
    44

    Talking INDEX MATCH query to include BLANK ENTRIES from source, so WEEK NUMBER obvious at a glance

    ROOM RENT paid of women, that pay me board, a spreadsheet I put together hastily.

    BRIEF INTRO:
    ~ There is a SUMMARY TAB 'TOTAL's FOR JAN-DEC', that reiterates the comments made, explaining why a payment was NOT made.
    ~ Simply there are SEPERATE SPREADSHEETS per MONTH that keep track of RENT PAYMENT AMOUNT.
    ~ Then PAID status is noted with either a 'Y' or 'N' in column D.
    ~ Then PAID (Cell C34) and NOT PAID (Cell C35), is totalled at the bottom of each MONTH
    ~ The H column refers to the 2 criteria's set by Y or N, in the summary tab, 'TOTAL's FOR JAN-DEC', Criteria1=B16, Criteria1=B17.

    VARIATION ON SAME THEME:
    I want the SUMMARY tab 'TOTAL's FOR JAN-DEC', which includes INDEX MATCH formula queries, to include BLANK ENTRIES from source tabs, hence then I can tell at a glance which WEEK NUMBER it is by glancing at the SUMMARY tab TITLE for the WEEK ROW, currently I have titled these REASON1, etc.
    Currently COLUMN D to I simply, note the NEXT available comment , however I want the summary tab to display the blank entries, hence these blanks are between the entries with comments! Obviously!

    EXAMPLE:
    'JAN' tab
    Comments for RENT UNPAID are made in CELLS: E3 and E17

    'TOTAL's FOR JAN-DEC' summary tab
    The 2 comments mentioned above, are listed one directly AFTER another:
    REASON1 then REASON2
    However the order they appeared in was WEEK2 (REASON2) and WEEK4 (REASON4).

    Unsure how to include blank entries in this fashion in the summary tab ('TOTAL's FOR JAN-DEC').


    Currently the formula looks like this for the summary tab for:
    JAN REASON1: =IF(ROWS($A$1:$A1)>$B$18,"",INDEX(JAN!E:E,MATCH(ROWS($A$1:$A1),JAN!$H:$H,0)))

    JAN REASON2: =IF(ROWS($A$1:$A2)>$B$18,"",INDEX(JAN!E:E,MATCH(ROWS($A$1:$A2),JAN!$H:$H,0)))

    As you can see my formula simple checks ROW by ROW for next written comment, which misses blanks entries.

    Sample File attached or this hyperlink www.srands.co.uk/RoomRent2012.xls for most recent file in works

    CROSSTHREAD/POSTED ELSEWHERE:
    http://www.ozgrid.com/forum/showthre...049#post600049
    http://www.mrexcel.com/forum/showthr...06#post3083106
    Attached Files Attached Files
    Last edited by srands; 03-16-2012 at 08:21 AM.

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: INDEX MATCH query to include BLANK ENTRIES from source, so WEEK NUMBER obvious at a gl

    Hello srands,

    I believe in keeping it short and simple. See the first three lines of your Summary Sheet in the WorkBook attached. Is that what you want to see?

    Also see the "Please consider" notes at the bottom of this Post.
    Attached Files Attached Files
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  3. #3
    Registered User
    Join Date
    07-26-2010
    Location
    Hull, England
    MS-Off Ver
    Excel 2003
    Posts
    44

    Re: INDEX MATCH query to include BLANK ENTRIES from source, so WEEK NUMBER obvious at a gl

    Hi, yes the 1st 3 columns on row JAN, that is how I want the RESULTS to be displayed.

    HOWEVER wouldn't that mean making a direct CELL REF for each WEEK/REASON? If so then that more tedious then I wanted, =JAN!E2.

    If so what I want is a formula to search & find COMMENTS & BLANK ENTRIES, and enter by order of WEEK/REASON in the SUMMARY TAB.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1