• 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.

How to change the Row or column colours automatically

K

Kumar@raja

Member
Dear Friends ,

Can anyone let me know how to change the Row or Column colours automatically upon completion of the work pertains to respective row or column?

I am working on data which contains 160 Rows. Here require to copy the data from above row to below and need to change the refering numbers by using find and replacement command to get the desired result. In this context, I have copied the first row data to entire 160 rows and changing the refereeing numbers manually. Here, colour of the completed rows to be changed automatically as a cross cheek. For an example if working on 9th row which means I have completed until row number 8th, thereby colors of the same to be changed automatically. This helps to avoid confusion whether I have changed the reference numbers or not? Also facilitates to generate error free report.

Hence, kindly help me out whether we can do the same by using any formula?

Thanks in advance,
Kumar
 
You can achieve this by Conditional Formatting, before that i need some info
you mentioned 160 Rows, but how many columns? Is there fixed range or dynamic range? Can we use helper column? Which type of data you have?

Assume that your data is in A1:E10
select your range-> conditional Formating->New Rule->using Formula-> then enter A1<>"" ->select formatting options i.e fill color-> then click OK

this formula will unfill the color wherever blank
 
Last edited:
vijay.vizzu said:
You can achieve this by Conditional Formatting, before that i need some info
you mentioned 160 Rows, but how many columns? Is there fixed range or dynamic range? Can we use helper column?
Click to expand...

Dear Mr. Vijay

Thank for the prompt response. Could you please let me know how can I do the same.

Thanks,
Kumar
 
Dear Vijay,

Please find attached herewith sample file... note that First row I have linked with orginal file and copied the same to entire rows so that from Column G by using Clt+F command with replacing of employee code data for the same will be captured from main source automatically . Here I want the change the row colour that I have finished to avoid confusion. Hope you understand.

Please help me out in this regard.
 

Attachments

  • Form 2 .xlsx
    94.6 KB · Views: 5
Kumar@raja said:
This helps to avoid confusion whether I have changed the reference numbers or not? Also facilitates to generate error free report.
Click to expand...


Hi Kumar..

Excel is designed such a way that.. after copy and paste.. it auto-adjust itself.. it but.. for this.. you have to learn Absolute & Relative reference logic..

In this particular case.. try to google Indirect Function..
change to formula accordingly.. like..

=INDIRECT("C:\path\of the\folder\[Copy of TDS Form 24 Q4 Workings .xlsm]" & $C3 & "'!$C$3")

When you will copy.. it will automatically adjust itself..
 
Hi Debraj,

Thanks for your view.

I have tired but the above formula is not working for my requirement. Actually the source of link was not mentioned in above formula.

Thanks in advance,
Kumar
 
Dear Mr. Vijay,

Anticipating a reply for the attached sheet. Please help me out.

Dear Mr. Deepak,

Could you please help me in this regard.

awaiting for valued support for the same.

Thanks,
Kumar
 

Attachments

  • Form 2 .xlsx
    94.6 KB · Views: 6
Dear Kumar...

Sorry, i was messed up with my Office Work... In your attachment ,i could not able to find that, how can i determine that i have completed the 1st row, 2nd row.... like so on. Which condition will say that, the row has completed? Can i assume that, if all the cells in a row has filled, then it is completed ? or do you have any such criteria which can determine that the row has completed?

Please share more, so that I or we can help you better in a short time.
 
Dear Vijay,

Thank you very much for your concern. as i mentioned above, upon completion of the work related to respective row , colour of the same to be changed as a cross check rather doing manually.

Thanks,
Kumar
 
Hi Kumar ,

I think Vijay's question has still not been answered.

The question is how can Excel be made to understand that data entry in a row has been completed ?

You have earlier mentioned that you copy the initial row and then paste it to a number of subsequent rows ; thus , row 4 , which is the first row of data , may be copied and pasted to rows 5 through 11. Now , you start entering correct data in the cells in row 5 ; when can we say that data entry has been completed in row 5 ?

Narayan
 
Hi Narayan,

Thank you for writing .

Your are correct. I am not clear in this regard. However, attached a sample file which I require the answer as ready reference. Kindly note that I have changed the colors manually. Here, colors indicates that work have been done for the respective rows and un colors to be completed.

Further, above our friend Mr.Debraj has given a nice formula to avoid manual intervention in this regard for doing the same in single click. Truly I don't how to apply same in this context to capture the data from main source. If you can help me in this regard would be greatly appreciated.

Here what I am doing is copied the first row data which was linked with main source and changing the respective employee ID by observing C3 with help of Ctrl+H command manually. I know manual intervention may leads errors if we are not attentive, As I don't know how to use the above formula doing the same manually.

Once again thank you all for the nice support and prompt response.

Warm Regards,
Kumar
 

Attachments

  • Annexure of Form 2 dummy for Testing.xlsx
    106.5 KB · Views: 5
Hi Kumar ,

To do what Debraj has suggested , first open both your files , the file you have uploaded , as well as the external file named Copy of TDS Form 24 Q4 Workings .xlsm

Now , in cell E3 of your uploaded file , enter the formula :

=INDIRECT("[Copy of TDS Form 24 Q4 Workings .xlsm]" & "'" & $C3 & "'!$C$3")

and see whether it returns the same value viz. 22/10/2009

If it does , you can change all the other formulae in row 3 , by the above formula , after replacing the highlighted portion with the appropriate cell address ; thus in G3 , the formula should become :

=INDIRECT("[Copy of TDS Form 24 Q4 Workings .xlsm]" & "'" & $C3 & "'!$H$27")

Please remember that if you do this , then the formula will return the correct results for all rows , as and when you put in the correct serial number of the employee in column C.

However , also remember that the INDIRECT function does not work with closed workbooks , so whenever you have this file open , you also need to have the external linked workbook open , for the formulae to return the correct results.

Narayan
 
Dear Narayan,

Thank you for taking time and explanation.

As directed by you did the same but it is not working.........

I have copied the formula as =INDIRECT("[TDS Form 24 Q4 Workings .xlsm]" & "'" & $C3 & "'!$C$3"), as source of the content from TDS Form 24 Q4 Workings and did the same by opening the both files, however is not fetching the result, and indicating cell as # REF! .

Hence, kindly let me know at your convenience where i have committed the mistake while copying the formula.

Thanks,
Kumar
 
Dear Narayan,

Thanks alot ... it is working now........ indeed very useful function. I am able to complete the task merely copying the data . Thanks lot......... The function seems similar to Vlookup , it is awesome..........

How it distinct with Vlookup ?

Really appreciated your valued help in this subject.

Best Regards,
Kumar
 
Hi Narayan,

Thanks a lot for your clarification and patience. I am extremely sorry for asking queries in pieces.

Could you please let me know the formula to know the date of joining between cut off dates.

Details as follows :

If someone date of joining was 20-10-2011, as the same was prior to 01-04-2013, I need the same to be shown as 01-04-2013. Further, if one joins 16-08-2013 the same should be shown as 16-08-2013. Here I need the accurate date of Joining which means joining during 01-04-2013 to 31-03-2014 will be shown as actual date of joining and who have joined prior to 01-04-2013 will be considered default as 01-4-2013 .

In this context which formula need to be used. hope you understand.

Herewith attached a sample for the same.

Thanks ,
Kumar
 

Attachments

  • Book1.xlsx
    10.2 KB · Views: 8
Hi Debraj,

Thank you for prompt response.

By default considering as 01-04-2013 where I need if some join after 01-04-2013 i.t 15-08-2013 it should consider as 15-08-2013,not 01-04-2013. If someone joins prior to 01-04-2013 it should consider 01-04-2013. Hope you catch my point.

Thanks,
Kumar
 
You must log in or register to reply here.
Back
Top