• 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.

Conditional using a table question

L

Leigh Digons

Member
Question. Is it possible to set up a conditional like this:

If Gloria Smith appears in a table, then use the number from column E row 1 in the table, otherwise use the number from column A in this tab.
 
Logical operation to see if "Gloria Smith" exists in table is as following.
=COUNT(INDEX("Gloria Smith",TableName),))>0
It will return TRUE or FALSE.

Nest this in IF statement.
=IF(COUNT(INDEX("Gloria Smith",TableName),))>0,TRUE,FALSE)

Replace TRUE/FALSE with your calculation/lookup operation.
 
I am trying to correct column AF in this first tab. If Person A appears in the table on the next page, I want it to return the January value in that row, otherwise I want it to return the beginning of year Standard FTE Column M
 

Attachments

Oh, I misunderstood you originally. You can use formula below in AF8.
=IFERROR(INDEX('Standard FTE Changes'!$C$17:$C$30,MATCH(B8,'Standard FTE Changes'!$B$17:$B$30,0)),M8)

Copy down.
 
See above. You don't need a table ;)

Edit: My first post assumed that "Gloria Smith" could occur anywhere on the table (not just single column).
 
Uploading latest file with your formula. For some reason, instead of returning the value for January FTE from the table it is returning the value in M8 of the first tab.
 

Attachments

My bad change reference to F column instead of C.
=IFERROR(INDEX('Standard FTE Changes'!$F$17:$F$30,MATCH(B8,'Standard FTE Changes'!$B$17:$B$30,0)),M8)
 
You already have Start and end of month stored in AD6 & AE6.

You can use cell reference to those instead of Date().
Ex. in AA: =(IF(AND($K8<(AE$6+1),$K8>(AD$6-1)),($K8-AD$6),0))
Copy down.
 
You must log in or register to reply here.
Back
Top