How to have excel automatically input pre-determined data into a cell?

[d_7231]

I need to have excel input an "X" in a cell, after a another cell has had data input in it.


For example, I need A1 to automatically display "X" after "XXX" has been manually input into B1.


However, I am not THAT fimilar with VBA or Macros so if there is a formula to input that would be preferable.


Thanks

 

[Hui]

d_7231


Firstly, Welcome to the Chandoo.org forums.


In A1 type

=If(B1="XXX","X","")

 

[d_7231]

Perfect! Thanks.


Now, what if I wanted to do this in a range of cells?

 

[Hui]

You can copy that down column A

Select A1

Drag the lower Right corner down

Excel adjusts the formula for each cell automagically

 

[d_7231]

Thanks Hui.


But I don't necessarily need to copy the formula to a range of cells, but I need to include a range of cells into the formula so that if any cell B1:B10 are input with "XXX", then excel will automatically (OR automagically) input "X" into A1.


So...=IF(B1:B10="XXX","X","") for example...but I have already tried this and it came back with "#VALUE!"


Any thoughts?

 

[shailyog]

in this case after entering formula don't just press enter, press Ctrl+Shift+Enter all together


or select the cell you have entered the formula >> press F2 >> press Ctrl+Shift+Enter all together

 

[d_7231]

@shailyog


Thanks, but I could not get the array function to not work. It did not show a cell error anymore, but only B1 out of the range of B1:B10 worked correctly.


I need A1 to return "X" if "XXX" is entered into any cell B1 through B10.

 

[Luke M]

We can do a COUNTIF to check if "XXX" appears within the specified range.

=IF(COUNTIF(B1:B10,"XXX"),"X","X")

 

[d_7231]

@Luke M


You're awesome dude. Thanks! It worked except I modified the "logical_false" to "" to keep the cell blank if nothing is entered.


Appreciate everyone's help!