Circular reference issue.

[Phaseshifter]

Hello,

So I've created a calendar, and I have the week ends set to auto adjust with the year/month. However, when I try to enter holidays with either "Count if" or "V lookup" function, I keep getting a "circular reference error," but I can't figure out why, as I'm not having the cell refer to itself.

Can someone tall me what my mistake is?

=VLOOKUP(B12,Jan!B2:B9,1,FALSE)


Attachment 752555
(Can't seem to be able to include a picture)

[Pete_UK]

It is not clear what you are trying to do. B12 contains a date, and your formula is trying to find a match in the range B2:B9 on the Jan sheet. All of the cells in that range are empty except for B2 which contains the letter V. Clearly, there is no match.

Perhaps you are trying to match with that same range on the Data sheet, which contains a list of dates related to holidays. However, by using VLOOKUP with a third parameter of 1, then if there is a match found the formula would only return the date which is already in cell B12. Maybe you are trying to find if there is a match in that list of holiday dates, and not return anything specifically other than TRUE or FALSE. If that is the case, then you could use this formula:

=COUNTIF(Data!$B$2:$B$9,B$12)>0

and this could be the rule used in conditional formatting to be applied to the cells on that sheet (if that's what you are trying to do).

Hope this helps.

Pete

[JohnTopley]

Perhaps

=VLOOKUP(B12,Data!B2:B9,1,FALSE)

[Phaseshifter]

No, it's looking for a match in the "Data" sheet, not the "Jan" sheet. At least it should be. Or am I reversing things? The table array are the values being looked up are they not?
I thought the third parameter was the number of columns being looked up. Maybe it was explained to me wrong.

But yes, I was trying to apply conditional formatting to highlight the holiday cells. I should have mentioned that.

I looked it up, and this is the formula I was using when doing the COUNT IF Function: =COUNTIF(Data!$B$2:$B$9,B$12)>0 It's the same, but when I tested it, all the dates came back as 0. (Along with the error message) so I assumed it wouldn't work, but when I applied the rule to the cond. formatting, it worked.

Can you tell me why the holiday dates don't show a 1 when looked up by the formula?

Also, How would I make this work when changing the year? If I try to set the date with a "=Date" it doesn't seem to like me referencing to the year and it stops working.

[JohnTopley]

See attached Months Jan and May

CF formula

=VLOOKUP(B$12,Data!$B$2:$B$9,1,FALSE)

[Phaseshifter]

Yes it works. But I need to find a way to make it change when I change the year. I probably have to link it to a reference cell.
I might have to create one per sheet

[JohnTopley]

It does work if you change the calendar year (AG10) AND the year in the holiday table!

Option is to have several years holidays (columns B, C etc) and use INDEX/Match based on AG10 to find the required year.

And you will have to repeat the CF for each sheet.

[JohnTopley]

Example

CF

=VLOOKUP(B$12,INDEX(Data!$B$2:$Z$9,,MATCH($AG$10,Data!$B$1:$Z$1,0)),1,FALSE)

with holidays for successive years in columns B onwards

[Phaseshifter]

It does work if you change the calendar year (AG10) AND the year in the holiday table!

Option is to have several years holidays (columns B, C etc) and use INDEX/Match based on AG10 to find the required year.

And you will have to repeat the CF for each sheet.

Yes that's what I though. That will probably work thanks.

=VLOOKUP(B$12,INDEX(Data!$B$2:$Z$9,,MATCH($AG$10,Data!$B$1:$Z$1,0)),1,FALSE)

I'll have to try this.