Forum Discussion
Need help with #N/A display for IF Function
- Sep 27, 2024
accounting1005 I apologize I missed that "4" in the column for the index. you just need to add INDEX or CHOOSECOLS so the following works:
=XLOOKUP(C5,JOB_NAME,CHOOSECOLS(JOB_DATA,4),"")
Or better yet I named your Jobs table (Table Design tab) JOBS so now you can just call out the column of interest (actually you could before but it was named like Table5 so it didn't mean a lot to the reader. But now:
=XLOOKUP(C5,JOB_NAME,JOBS[Tip Share],"")
so you can clearly see you are returning "Tip Share" value out of the JOBS table.
See attached I updated Monday
This is real hard to tell without having the sheet to look at but that formula is correct if column C is blank and will be filled in. If, however, column C is a formula like =A5 or =IF(ISBLANK(A5),"",A5) or something else then technically it is NOT Blank. maybe try =IF(C5="","", ...
on another note you should consider using XLOOKUP instead of INDEX( MATCH()) and then you can also set a if_not_found value = "" so something like:
=IF(C5="","",XLOOKUP(C5,JOB_NAME,JOB_DATA,""))
and probably won't even need that condition then so:
=XLOOKUP(C5,JOB_NAME,JOB_DATA,"")
othewise maybe attach a sample sheet without private information and we can see better what is going on.
- accounting1005Sep 27, 2024Copper Contributor
m_tarler Thanks for your reply! I'm not advanced with excel, and tried the two suggestions you made but I got a #Spill! error. I couldn't attach a sample sheet because .xlsm is not supported. Is there a different format I should save the sample sheet as or a different way to attached the sheet?
Edit: I think I'm able to send you the sheet via private message. Let me try that instead.
- m_tarlerSep 27, 2024Steel Contributor
accounting1005 I apologize I missed that "4" in the column for the index. you just need to add INDEX or CHOOSECOLS so the following works:
=XLOOKUP(C5,JOB_NAME,CHOOSECOLS(JOB_DATA,4),"")
Or better yet I named your Jobs table (Table Design tab) JOBS so now you can just call out the column of interest (actually you could before but it was named like Table5 so it didn't mean a lot to the reader. But now:
=XLOOKUP(C5,JOB_NAME,JOBS[Tip Share],"")
so you can clearly see you are returning "Tip Share" value out of the JOBS table.
See attached I updated Monday
- accounting1005Sep 28, 2024Copper Contributor
m_tarler Thanks for editing the formula! When I copied the Monday sheet with your code to duplicate the other weekdays, and reworked the sum on the summary sheet, I ended up getting value errors. I feel like I made this more complicated as I might've asked for help with the wrong thing. In retrospect, I should've mentioned the goal that got me to these errors in the first place. In the old file, my managers would have to select the employee positions everyday for the codes to work. I wanted to make this easier for them by having them set the information on the Weekly Summary sheet. I was able to make some progress with the edits but I think it would be best to break down how I got here to my original post.
I will have to message you the file again but here is what I did.
On the Weekly Summary sheet, I added column D (Position) as it was not there before. I also turned column B,C,D into filters so the information can alphabetize should the team need to add new staff and sort the names.On the daily sheets, column C had always been there but it had a drop down function that lists the positions. You can see this on Tues - Sun. I was able to remove the dropdown list and use "='Weekly Summary'!D4" as a reference for that column. However, once you get to the cells that don't have information, the zeros and errors will show up. I was able to format column C to hide the zeros but the #N/As for the rest of the row is still there and gets reflected on the summary sheet. This was how I got to the original issue I inquired about.