• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

move cell content without conditional formatting

A

A Trivedi

New Member
Need your help. I have created an excel sheet where multiple names are written in different cells of one row. Multiple such rows are there. below name rows, there is 1 row corresponding to each row containing numbers. Duplicate name entry contains 1 others will have 0.

For e.g.


Kat Jones Bill Jones Bill

Mac Paul Lope Hunny Mac

......................................

0 1 1 1 1

1 0 0 0 1


I have applied conditional formatting on names row. First name row is associated with first number row. If corresponding element in number row is 1 then name will be highlighted with red color. My formula works fine.

But if I try to move the name by dragging it to some another cell, then name moves with conditional formatting. I want to move the cell by dragging but without conditional formatting. I want to preserve (freeze) the conditional formatting on the original cell.

Help me.
 
If you move the name by dragging it, you are literally moving the cell, and everything that goes along with it. You could instead try copying the cell value. Or, you could change the way you have the conditional formatting setup. Instead of using a helper row, CF formula could just be:

=COUNTIF(2:2,A2)>1

Then, if you move the cell to another row where there aren't any duplicates, it would no longer be highlighted.
 
Welcome To Chandoo.org!!!


When you drag down the handle then small floating button appears, click on its dropdown it'll show options like:


Copy Cells

Fill formatting only

Fill without formatting


Try the third option.
 
First of all thanks for quick reply. but my problem is not solved yet. actually the helper row is generated from some formula. Value 1 in helper row is an indication of duplication of names. So whenever duplicates are removed then automatically the helper row will be updated through formula. Duplication is removed by moving highlighted cell to some another row and if moved cell is again duplicated in new row then the conditional formatting of that row should highlight both of them.

My requirement is that conditional formatting should work on same row where it is applied. If new value is coming in that row then it should work on new value instead of moving with cell.

E.g.

Kat Jones Bill Jones Bill

Mac Paul Jones Kat Mac

......................................

0 1 1 1 1

1 0 0 0 1


If I will move Jones from 1st row to second row to remove duplicates,then it should highlight both Jones in second row. Same way if I will move Kat in first row in place of Jones then it should highlight Kat because it is duplicated.


the countif formula will do this? if yes then please tell me how to implement it with helper row. Please help me.
 
Do you need the helper row for some other reason? Either way, there's no need to connect the CF to the helper row, they can be independent of each other. The helper row will actually be the one to struggle more because it's locked on a specific cell which is moving. More on this below:


As described above, let say you select row 2 with A2 being the active cell. CF formula is:

=COUNTIF(2:2,A2)>1


Because everything in the formula is relative and in relation to the cell, if you move the cell somewhere else, you'll be okay. For instance, if you move to row 3, formula will become:

=COUNTIF(3:3,A3)>1


which is what we want.


Helper row:

Since we want to lock onto a specific location and not a specific cell, we'll use the INDIRECT function. We just need to make note of the row number of first helper row. I'll assume it's row 10. If this is looking at row 2, we note a difference of 8. Formula then is:

=COUNTIF(2:2,INDIRECT(ADDRESS(ROW()-8,COLUMN())))>1


Make note of the -8 in the formula as this will need to be changed to fit your exact setup. The formula will now always look at the cell in row 8 rows above the cell w/ formula, and in the column it is in. Should be able to copy the formula down and to the right as needed.
 
I have tried to implement your formula. But I am not getting desired result. Let me explain my entire problem in detail.

I have an excel sheet of time table of a school. This sheet contains multiple rows with name of a teacher having lecture in a particular class. Header row indicates class and leftmost column represent lecture timings. Looks like below.


Grade1 Grade2 Grade3 Grade4

10 to 11 John Bill Kat Shelly


11 to 12 Bill John Kat Nathan


etc.


Now I am preparing daily time table with excel sheet. Changes are required in time table if somebody is absent or not available due to some assignment. So I need to drag the name of faculty to re schedule the timetable.


I have created hepler row for each time slot row. This hepler row gets the value from the formula which is available on some another worksheet of same workbook. This formula

basically calculates whether one teacher has given more than one class in a particular time period. If John is present in 10 to 11 slot - more than one time then corresponding cell of both John entry will get value 1.


I want to highlight both John cell to indicate duplication.

Now if I will drag Kat from 11 to 12 slot at the place of Shelly, it should refer corresponding element of helper row i.e. which was previously being used by Shelly.

My helper row is updating data accurately when I drag cells. i.e. If I will take Kat to Shelly, the corresponding helper row cell for both Kat will contain 1 as there is a duplication of Kat.


Well I am on completion of my excel sheet but this is the main feature that I want to implement. I would be thankful to you if you help me to solve this.
 
Hi Trivedi ,


I think there is some confusion over your problem ; let me simplify the question in my own words.


1. You have a set of names in several time slots , with multiple names in each time slot ; these names are manually entered.


2. You have a set of formulae which determines whether any name in a row is duplicated ; you say these formulae are working correctly.


3. You have conditional formatting which refers to these cells to decide whether the duplicated names should be highlighted ; you say this conditional formatting is also working correctly or am I wrong ?


4. Your problem is when you drag a name from one cell to another on the same row , the highlighting does not happen ; am I correct ?


Can you please post the conditional formatting rule that you are using ?


Narayan
 
Hi Narayan,

Yes you understood correctly.

My conditional formatting rule is as follows.

=b$97>0 then highlight cell with yellow color.

Now, b97 is associated with let us say b12. b,c, d ...etc columns of 12th row contains name of faculties engaged in first lecture. b97 contains 0 if faculty whose name is typed in b12 is unique in entire 12th row. it will be 1 if name duplicated 1 time in 12th row.

This conditional formatting is applied on all columns of 12th row.


when I drag a name from one cell to another on the same row or elsewhere, the formula moves with cell.

I want that formula must be locked in the same cell so that if i will drag / type name of another faculty in b12 then also it should follow conditional formatting for new entry.


b97 will be automatically updated for new faculty name. i have checked. it is working.

my only problem is to lock formula.

Help me.
 
Hi Trivedi ,


I am not very clear on your problem , since 2 weeks have passed since my last post ; offhand I would like to ask you why the CF rule is =B$97>0 ; why not =B97>0 ? After all , if the formula for detecting duplicates is working correctly , each cell in the data range will be conditionally formatted based on the corresponding cell which has either 0 ( not a duplicate ) or 1 ( a duplicate ).


Can you try this and see if it works ?


If not , please upload your workbook.


Narayan
 
You must log in or register to reply here.
Back
Top