ROOM RENT paid of women, that pay me board, a spreadsheet I put together hastily. Sample File this hyperlink www.srands.co.uk/RoomRent2012.xls
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').
Other then tedious direct cell ref like =JAN!E2 in summary tab, etc.
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 this hyperlink www.srands.co.uk/RoomRent2012.xls
CROSSTHREAD/POSTED ELSEWHERE:
http://www.excelforum.com/excel-gen...eek-number-obvious-at-a-glance.html?p=2735156
http://www.ozgrid.com/forum/showthread.php?t=163437&p=600049#post600049
BRIEF INTRO:
~ There is a SUMMARY TAB 'TOTAL's FOR JAN-DEC', that reiterates
~ 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').
Other then tedious direct cell ref like =JAN!E2 in summary tab, etc.
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 this hyperlink www.srands.co.uk/RoomRent2012.xls
CROSSTHREAD/POSTED ELSEWHERE:
http://www.excelforum.com/excel-gen...eek-number-obvious-at-a-glance.html?p=2735156
http://www.ozgrid.com/forum/showthread.php?t=163437&p=600049#post600049
Last edited:
