Thread: Insert value to a different cell based on condition

Hello Guys,

I have a problem with a pivot table as it is reading a formula within a cell as a value and is making a wrong count. I came out with an idea that I will put a formula in different cell outside of the pivot table's range to calculate and insert a value to a cell in the range.
My problem is that I don't know how to do it.

In cell E1 I have a validation list to chose from: blank or Yes. In cell F1 I put a formula:

=if(E1="Yes","","Yes")

That causes a problem as the pivot table reads the formula as a value iven if the outcome is "" whixh is blank.

So I am thinking about placing this formula in a cell K1 (which is out of range) but making it to insert the yes/no value of the if condition into the cell F1.

Is it possible? and If it is can anyone help me out with the info what kind of function shall I use?

Anybody who knows please step forward.

Thank you for reading it and giving it a go.

Simon

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Sorry for not providing an example file. I thought that maybe someone would know off the top of one's head.

Please change the cell adress to row 2 instead of 1.

Thanks again for trying.

Simon

What Excel version are you working with?

It is 2000.

Sorry, what does this mean?

Please change the cell adress to row 2 instead of 1.

Did you refresh the tables after changing the drop down selection?

Yes, it was still coming up as 1 on not chosen count. I think it just sees the formula in the cell and takes it as a value.

I think the best bet is to add 2 helper columns to your table, you can hide these later, and resize your pivot tables to include these columns.

Use a formula in G2:

=--(E2="Yes")

and in H2

=--(F2="Yes")

These will return 1's and 0's instead of Yes and blanks.

then in the Pivot Table, sum these columns instead of counting the Yes/blanks.

See attached.