Excel conditional formatting with Weeknum

[CaliMIke]

what i trying to do is simple but im not having any luck.

Im try to get conditional formatting to use the weeknum function to populate a column based on another column date values. i dont need the weeknum column to change colors i just need it to display the weeknum value.

The reason im using conditional formatting is that i want that column to always have that function without being deleted.

i have tried =weeknum(a2) for cell b2 but no results. i have tried an if function but also no luck.

 

[Becky]

Welcome to the forum @CaliMIke

As far as I know, conditional formatting can only change the format, it cannot generate data unfortunately. Have you thought about using a formula and then putting some protection on the cells so they can't be deleted?

Lock cells to protect them

 

[CaliMIke]

I have but it is a spreadsheet that other people will be using and they will need to delete and add rows so i was hoping to have conditional formatting for a cell range of B1:B1000 that will never change the formatting . They are currently using about 650 row of data and i created a week number column from the date of a follow up appointment to create a slicer that uses the week column to generate basically the current week for follow up appointments that need to be addressed.

So the problem i am having is when they insert a new row between existing rows the =weeknum function does not get carried into the new cell but all my conditional formatting does that is why i was trying to go that why. I dont know any other way to automatically have a function is a cell when a new row is created.

 

[Becky]

Ah ok, I see the problem. Have you tried using a Table within the worksheet? Select the data, go to Insert > Table then tick the box for headers if they are included in your selection. When you add a new row in a table it should automatically copy the adjacent formula(s) into the relevant cells. It's a nice way to do it without using VBA.

 

[CaliMIke]

It worked. I had to copy the spreadsheet, clear the data, covert the table to a range, input the weeknum formula in the column, then covert it back into a table, then copy and paste the data back into the new table.

Thank you for your time.
Mike