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, Software Specialist
Category: Microsoft Office
Satisfied Customers: 30236
Experience:  Over 15 year experience resolving Microsoft Office Issues
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?
Submitted: 1 month ago.
Category: Microsoft Office
Expert:  Richard replied 1 month 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


You can then copy this down as far as you wish

Let me know please how you go

Customer: replied 1 month 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 1 month 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