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

Formula to change the row number automatically

Jack-P-Winner

Jack-P-Winner

Member
Is there a formula I can use to change the B to a different letter like say E automatically? I have a lot of conditional formatting I need to change and I am thinking if I can run code to change them kind of drag and drop , then I can copy and paste them rather going in individually and change 1 at a time


Change this

=AND($B2=6,$B3=2,$B4=2, $B5=2, $B6=4)

To this

=AND($E2=6,$E3=2,$E4=2, $E5=2, $E6=4)

Thank you all!
 

Attachments

sorry, I should have clarified, I have these in conditional formatting and I need to change a lot of them. I do not think find and replace will work for that
 
Raaz, I want to thank you, I just copied them all to a blank sheet with separate tabs and replaced the letters again, Thank you!!!
 
Just a reminder that Find and replace will change all occurrences of the letter B to E

So If your formula had been
=ABS( AND($B2=6,$B3=2,$B4=2, $B5=2, $B6=4) )

you would end up with
=AES(AND($E2=6,$E3=2,$E4=2, $E5=2, $E6=4) )
which will return an error

Instead use Find/Replace and search for $B replace with $E
=ABS(AND($E2=6,$E3=2,$E4=2, $E5=2, $E6=4) )
 
Hi Larry ,

This is not meant to criticise , but the simple fact is that if you can plan your worksheets / workbooks , then you know that you need to copy either formulae or CF rules across columns , rows or both ; knowing this in advance you can easily draft the formulae / CF rules so that they are copy friendly.

The $ sign in the rule :

=ABS( AND($B2=6,$B3=2,$B4=2, $B5=2, $B6=4) )

is not at all necessary if you know in advance that you would like to copy the rule from column B or which ever column it is being developed for , to any other column.

If you copy this same rule / formula across rows , the absence / presence of the $ sign is immaterial. The $ sign is significant if you intend to copy this across columns ; if you want it to change , then putting in the $ sign , and then replacing the column reference to B by E is just doing in a roundabout manner what you could have done in the first place more easily by developing the formula without the $ sign.

What I mean to say is that , even now , if you know that this formula will need to be copied to many other columns besides column E , then it may be worth your while to change the formula in its original column itself , by removing the $ signs there. Thereafter , you can copy the formula to any other column , and Excel will adjust the references correctly. No more Find and Replace !

Narayan
 
Thank you Hui, thus I know (-:

Narayan,
nothing from you I take in criticism I take it like a student would take from his teacher. Many people find it hard to believe I'm still learning the simplicity if excel by looking at my work You've REALLY helped me a lot At the time I had no intentions of copying the formula to other columns but as I progressed and saw the potential of using this to my advantage I found myself needing to copy and paste these

Always feel free to give me constructive criticism and again THANK YOU for all your help and sticking with me through my project

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