Forum Discussion
What am I doing wrong?
I have been trying to update this formula. If the date showing in current month matches the date on my helper file then display this information. I have tried and tried and cannot get this formula to work. I continue to get #value.
The formula is
=IF($A$4=HELPER!$B$4='CCM Helper'!$C$4,IF(HELPER!B6,XLOOKUP(CCMTotalsSP[@[Pod_2]],CCMTotalsSP[Metric],CCMTotalsSP[Pod_2]),IF($A$16=HELPER!$B$16,XLOOKUP(C3,LMCCMTotalsSP[Metric],LMCCMTotalsSP[Pod_2]),"")))
The workbook has to be updated daily, the person who owned the file quit the company and left no instructions on month end for this work book. The data that is needed to show is there but since I changed the month to August it won't populate for some reason if though the helper files says its August 2022. I am unable to share the file because it has name and states August 2022, look up the potentially billable number from this cell (C4) in the ccm helper file(the number changes daily). I don't care about adding last months information as a second option since that data is inaccurate.Let's see if we can break down the formula:
=IF($A$4=HELPER!$B$4='CCM Helper'!$C$4, this conditional clause already is problematic in that it compares three values, A with B with C; was that with the original?
IF(HELPER!B6, in this next, nested conditional, there is no comparison
XLOOKUP(CCMTotalsSP[@[Pod_2]], so I'm going to stop analyzing.
CCMTotalsSP[Metric],
CCMTotalsSP[Pod_2]),
IF($A$16=HELPER!$B$16,
XLOOKUP(C3,LMCCMTotalsSP[Metric],
LMCCMTotalsSP[Pod_2]),"")))
Is it possible that the original formula--which, I'm presuming, did work--is still available to view? Is it possible that it was an IFS function rather than an IF function?
mathetes The original formula was this
=IF(TODAY()=HELPER!$B$4,D4,IF($A$4=HELPER!$B$6,XLOOKUP(C3,CCMTotalsSP[Metric],CCMTotalsSP[Pod_5]),IF($A$4=HELPER!$B$16,XLOOKUP(C3,LMCCMTotalsSP[Metric],LMCCMTotalsSP[Pod_5]),"")))
I hope it's not too unreasonable to ask that you give us more than a very small image to work with.
- First, could you just copy that formula--I assume the one you're talking of is the one in the formula bar in the image--....copy that formula in full into a new message. It's not at all easy to read from your image, and asking us to do that AND debug it, well, that's not reasonable (IMHO).
- Second: explain each of the references IN the formula.
- I notice you say in the title that you're trying to update the formula; that implies it was working in the past and you've been making some changes. What are the changes? If you revert to the original, does it still work?
- Fourth: if it's possible, please post a copy of the actual file, either here or in one of the many cloud services, and provide a link. Just make sure there are no pieces of confidential or private information in that file.
It's difficult to know what's going on without seeing the workbook, but I notice that the condition in the second IF is just HELPER!B6. There is no = or other comparison operator in that condition. Are you sure that is what you intended?
- This worksheet was created by someone else and I am trying to update and fix issues but I am having a hard time rewriting the formula. Is there a way someone can view the worksheet and help?
Could you attach a sample workbook (without sensitive data) to a reply or private message, or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.
