Aug 08 2022 07:25 AM
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.
Aug 08 2022 07:33 AM
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?
Aug 08 2022 07:38 AM - edited Aug 08 2022 07:41 AM
I hope it's not too unreasonable to ask that you give us more than a very small image to work with.
Aug 08 2022 08:03 AM
Aug 08 2022 08:11 AM - edited Aug 08 2022 08:13 AM
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.
Aug 08 2022 08:15 AM
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.
Aug 08 2022 09:00 AM - edited Aug 08 2022 09:22 AM
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?
Aug 08 2022 10:40 AM
@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]),"")))
Aug 08 2022 10:41 AM
Aug 08 2022 11:06 AM - edited Aug 08 2022 11:08 AM
Is that change of date, from July to August, what's captured in cell $B$4 of HELPER! ? If not there, where is that change of date (what cell)?
How exactly are you changing the date? Specifically what are you doing? Are you using Excel date conventions OR might it be possible that you're entering the August date as a text?
Is it possible, in other words, that it's not the formula that's at fault, but rather the way you're entering the new date? [It's a question I have to ask, because that's a very possible cause of the "formula not working"]
Aug 08 2022 11:17 AM
Aug 08 2022 11:40 AM
Aug 08 2022 11:47 AM - edited Aug 08 2022 11:47 AM
In cell $B$4 the date 8/1/2022 is captured. I am changing the month by typing in August 2022 as text. I think it maybe the way I am adding in the date.
I'm quite sure that's what's happening. Read up here on how Excel handles dates.
Aug 08 2022 12:03 PM
Aug 08 2022 12:36 PM
I was able to change the date as suggested and it did not display data. However I do not have an error in that box anymore.
That's a bit difficult to understand. Good that you have no error, I guess, but "did not display data" doesn't sound so good. What exactly was your point?