What am I doing wrong?

Copper Contributor

clowery000_0-1659968634662.png

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. 

15 Replies

@clowery000 

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?

@clowery000 

 

I hope it's not too unreasonable to ask that you give us more than a very small image to work with.

 

  1. 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).
  2. Second: explain each of the references IN the formula.
  3. 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? 
  4. 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.
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?

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.

@clowery000 

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.

@clowery000 

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]),"")))

But it doesn't work when I changes the date from july to august.

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"]

With the change of date I get an #value error, 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.

@clowery000 

You can get the date to display as August 2022 with cell formatting:

 

Patrick2788_0-1659983992303.png

 

@clowery000 

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.

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.

@clowery000 

 

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?

I was able to figure it out, thank you all!