Hi,
I have a table like this:
Header 1 Header 2 Header 3
1 - 20
2 20 30
3 30 50
4 - 40
I would need a formula that replaces all "-" in Header 2 with the value in the cell next to it (Header 3).
See below:
Header 1 Header 2 Header 3
1 20 20
2 20 30
3 30 50
4 40 40
Any simple Formulas for this? I have tried for a while now but can't seem to get it to work properly.
Thank you.
Last edited by wearonourway; 07-20-2015 at 10:47 AM. Reason: Hope you understand it, even though my table got a bit different compared to when I previewed it.
say header 2 is in column B
-IF(b1="-",C1,b1)
If I've helped U pls click on d *Add Reputation
Another option if header 2 is in column B...
Place this in B1 and drag down...
or
Please Login or Register to view this content.
Either one should work...if I understand what the results you want are correctly.Please Login or Register to view this content.
1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.
Tom
So I have to add one more column between Header 2 & 3 and then use this formula. Is my understanding right?
Yes. or you may use the next available column then copy paste the results
With my formula, you wont have to add another column
Originally Posted by bhenlee
Thanks for your suggestions, however I can't seem to get it to work properly when I "drag down" the formula.Another option if header 2 is in column B...
Place this in B1 and drag down...
orPlease Login or Register to view this content.
Either one should work...if I understand what the results you want are correctly.Please Login or Register to view this content.
I have uploaded a sample file, if anyone wants to have a go.
Attachment 408393
in cell D2 copy & paste this formula:
=IF(B2="-",C2,B2)
then copy the result on column D and paste it on column B
If you are using a helper, put this in it and copy down...
=ABS(A1)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
My mistake. Mine will only work if the data is already filled in. If it's not and it is manually (or otherwise) placed into the cell, it removes the formula. Sorry about that.
I understand now and got it working. But I am looking for a solution where I don't need to use a helper column.
Let me explain. I want to update two certain columns with raw data every month. The right column always have a value like 50, 40, 30 etc. No problem there. The left column also got values, but a few doesn't have values and "-" is displayed in the cell. For these ones only I want to replace the "-" with the value that is right next to it in the right column. Like IF "-" is found, copy the value that is in the column to the right and replace "-". IF no "-" is found then do nothing. Thanks for your help and patience.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks