If Q244=1 then enter 1, if Q244=1 then enter 1 in cell R244 and delete contects of Q244, if Q244=2 then enter 1 in cell S244 and delete contents of Q244, if Q244=3 then enter 1 in cell T244 and delete contents of cell Q244, if Q244=4 then enter 1 in cell U244 and delete contects of Q244 Basically I have 5 columns of data, in column Q each cell has a different number that needs to be changed to a 1 then entered in the corresponding column. Is this even possible? I've spent several hours trying to come up with a formula & just when I think I have it, I get the wrong answer! Thanks for your help.

Let's begin by stating right up front that a formula can not delete anything from any cell. A formula can only return a result. That result might be 0 or "" (nothing) but the cell will still contain the formula. Now, as to what you are asking for, I'm confused right from the start. The first part of your very long sentence says:

If Q244=1 then enter 1, if Q244=1 then enter 1 in cell R244...Is that a typo? It doesn't make much sense.

Sorry it is a bit long. No it's not a typo. Currently the data that I need to report on is all in column Q, represented by numbers 0 through 4 (0=home, 1=small office, etc.) I am trying to create a pivot table to keep track of what our customers are buying and the only I could come up with is to move what is in column Q to the approriate column and replacing it with a #1 so I can count it in my pivot table. Basically my spreadsheet looks like this: Col Q Col R Col S

Home(0) Sm Office(2) Enterprise(3)

row 10 3

row 11 0

row 12 2and so on.....

So what I was thinking is I could create a formula that said "if the value in Q10=3 (or 1, or 2, etc.) then to put a 1 in cell R10 and zero out Q10"

I'm not sure if this makes sense but I really do appreciate the help.

re: No it's not a typo.You said:

If Q244=1 then enter 1, if Q244=1 then enter 1 in cell R244That's not a typo? Why do you repeat the words "If Q244=1 then enter 1"

twice?re:

"if the value in Q10=3 (or 1, or 2, etc.) then to put a 1 in cell R10 and zero out Q10"The first part is easy:

In R10 enter:

=IF(Q10 = 3, 1, "")

However, as I said earlier, you can not use a

formulato "zero out" a cell after entering a number in it.The only way to zero out a cell that after manually entering a number in it is with VBA. However, if you delete the value in Q10, then the value in R10 will change also - unless you use VBA to also lock the value in that cell.

Why can't you use something like COUNTIF to get the values you need?

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History