Excel conditional formatting with Weeknum


Joined
Oct 7, 2018
Messages
3
Reaction score
2
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.
 
Ad

Advertisements

Joined
Oct 7, 2018
Messages
3
Reaction score
2
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

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
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.
 
Ad

Advertisements

Joined
Oct 7, 2018
Messages
3
Reaction score
2
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top