How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Richard Your Own Question
Richard
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 35413
Experience:  Over 15 year experience resolving Microsoft Office Issues
32989067
Type Your Microsoft Office Question Here...
Richard is online now
A new question is answered every 9 seconds

I have a large SS...39 columns by 395 rows. The cells

Customer Question

I have a large SS...39 columns by 395 rows. The cells contain either a lower case x or are blank. I'm having trouble writing an IF statement that returns a 1 in the cells that currently have an x, and leave a blank in those cells that are currently blank. I wish to write one statement only and then copy it throughout the SS. Is this possible?
Jim
Submitted: 8 months ago.
Category: Microsoft Office
Expert:  Richard replied 8 months ago.

Hi there and welcome

this is possible, lets say the the cells are in column A, starting in B1 to check A1 you would use

=if(A1="x",1,"")

You can then copy this down as far as you wish

Let me know please how you go

Customer: replied 8 months ago.
The statement/formula you provided will place the 1,or blank, in an adjacent cell in the column under the formula. I need the actual cell that contains the blank to remain blank, and the actual cells that contain "x" to be transformed/changed to the value 1. I can't have the the neighboring cell to turn blank or a 1 because the formula when copied throughout the SS would then return erroneous data. The formula must iterate throughout the SS without changing the cell values other than turning an x into a 1.
Expert:  Richard replied 8 months ago.

ok, formulas cannot change the value of another cell, only the one they are in.

What you need for this is a macro, which is coding.

Have you written a macro before Jim? If not, I would have to code it for you and for this you need to accept the request for the additional service and then provide me the sheet please