• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Look down current month column and select first value without *

T

tpheath

New Member
I have a sheet with a column for each month. The column contains both numbers and *. I need to look down that column and show the value for the first cell that does not contain a *, for the current month. (I should mention that cell N1 has the current date)


I need another one that will display the name from colmn A for the first non * value in the correct month column.


I also would not mind if these to values were concantenated into 1 cell, rather than using two formulas.


Can't seem to figure this one out!


Example: For January I need to get Lisa in 1 cell and 3 in another cell.

For February I need to get Jeff in 1 cell and 2 in another cell.


Name.....January.....February

Jeff...........*................2

Tom..........*................*

Lisa..........3................1
 
TPHeath


For the first part try:

=INDEX(OFFSET($A:$A,0,MATCH(DATE(YEAR($N$1),MONTH($N$1),1),$B$1:$M$1,0)),SUMPRODUCT((OFFSET($A$2:$A$5,0,MATCH(DATE(YEAR($N$1),MONTH($N$1),1),$B$1:$M$1,0))<>"*")*ROW(OFFSET($A$2:$A$5,0,MATCH(DATE(YEAR($N$1),MONTH($N$1),1),$B$1:$M$1,0)))))


For the second question, have a go at this which will find the name:

=INDEX($A:$A,SUMPRODUCT((OFFSET($A$2:$A$5,0,MATCH(DATE(YEAR($N$1),MONTH($N$1),1),$B$1:$M$1,0))<>"*")*ROW(OFFSET($A$2:$A$5,0,MATCH(DATE(YEAR($N$1),MONTH($N$1),1),$B$1:$M$1,0)))))


I have assumed your data is in A1:M5
 
Hui!


Amazing! Thank you so much for your response. I think that I am missing something or doing something wrong. I am getting and NA on both. It appears that this is occuring when getting the month from N1. I have typed the date in manually and also used the Today() formula. Each time I get the same error. I have tried to strip down the formula to just get the matching month and get the same error.
 
Where you have January, February in B1..M1

Change those to 1/1/2010, 1/2/2010 etc

Forgot to mention that
 
I posted dd/mm/yyyy as the month format but if you are using mm/dd/yyyy in your country did you try 1/1/2010, 2/1/2010 etc ?


If still no joy can you post your workbook somewhere ?
 
I changed the date format to English UK so that the dates were formatted the same. Still had same issue. I have posted the workbook here: http://docs.google.com/leaf?id=0BxhVKid9p8IBOGE5OGU3NTUtNjA2ZS00NmJlLWE0M2EtZWZlNTE2MDYwMDI4&sort=name&layout=list&num=50


The main goal is to look into the current month column (and this will be used in multiple years) and copy the name and numbers of those not listed with a * to a new list. This list won't be on this sheet, it will be elsewhere, but I can easily change the reference. I also can't do a whole lot to modify this sheet, as it is another departments and I am just trying to pull information off of it.


You should have edit access, and not need any password to access the file.


I appreciate your help. I know this takes time for you to think about and look at.
 
I'm getting an error when I put the above "docs.google..." link into a browser:


Sorry, the page (or document) you have requested is not available.
 
Sorry, give this a try:

http://docs.google.com/uc?id=0BxhVKid9p8IBOGE5OGU3NTUtNjA2ZS00NmJlLWE0M2EtZWZlNTE2MDYwMDI4&export=download&authkey=CMvXxYMI&hl=en


If this does not work, I could provide me email address, you can email me and I can reply with the document.
 
Tpheath


I would make the following changes to your spreadsheet and hope you are happy with the outcome:

1. Change B5:M5 to date and in format 1/1/2010, 1/2/2010 d/m/y and change display type to be MMMM if you want to see the Month Names

2. I would get rid of the * in the data area and just leave the cells blank

3. For the No and Names use the following bad boys


for the Number:

Code: 
=INDEX(OFFSET($A$6:$A$70,0,MATCH(EOMONTH($N$1,-1)+1,$B$5:$M$5,0)),SMALL(IF(OFFSET($A$6:$A$70,0,MATCH(EOMONTH($N$1,-1)+1,$B$5:$M$5,0))<>0,ROW(OFFSET($A$6:$A$70,0,MATCH(EOMONTH($N$1,-1)+1,$B$5:$M$5,0)))),1)-5,1)


for the Name:

=INDEX(A6:A70,SMALL(IF(OFFSET($A$6:$A$70,0,MATCH(EOMONTH($N$1,-1)+1,$B$5:$M$5,0))<>0,ROW(OFFSET($A$6:$A$70,0,MATCH(EOMONTH($N$1,-1)+1,$B$5:$M$5,0)))),1)-5,1)


IMPORTANT - The above 2 formulas are Array Formulas so after you copy and paste them you dont press Enter you need to press Ctrl Shift Enter


4. If you are really hung up on having *'s I would enter 0 in those cells and the change the formatting to #,##0;-#,##0;"*"

That way the 0's will show up as *'s
 
Hui,


Sorry it has taken me so long to respond. I have been out of my office for a while now. Thanks for your effort on this. I will be making those changes!


Thanks again
 
Works great! Thanks.


If I want to get the 2nd, 3rd, 4th, entry in the column, which numbers would I change in the formula above?
 
OK, I have played around with the formula and am stuck again. Anyone know how I can use this to get the 2nd, 3rd, 4th, etc values that are >0?
 
for the Second, Third etc

Change the very end of both equations


From: ,1)-5,1)

To
Code: 
:
,x)-5,1)


where x is the number 2, 3 4 etc


You could also use

To: ,A1)-5,1)

Where A1 will have the value you are looking for


IMPORTANT - The above 2 formulas are Array Formulas so after you copy and paste them you dont press Enter you need to press Ctrl Shift Enter
 
You must log in or register to reply here.
Back
Top