Display result in a different cell

[Paul Herron]

The formula below is in cell A1:

=IF(U482="Fixed Price",0.001,"")

How do I get the value if true (0.001) displayed in a cell other than A1?

(I'm new but learning fast -- thanks.)

 

[Greg Truby]

Simply copy and paste the formula into the other cell. If you want the new destination cell also looking at cell U482, then you need to hit your F2 key and copy the formula from the formula bar. You can then simply paste into the new cell.

[An alternative is to make the reference to U482 absolute, but the F2-way is a bit simpler.]

<SUP>edit</SUP> Um, or you could just put =A1 in the other cell. doh! <SUB>/edit</SUB>

 

[Paul Herron]

Simply copy and paste the formula into the other cell. If you want the new destination cell also looking at cell U482, then you need to hit your F2 key and copy the formula from the formula bar. You can then simply paste into the new cell.

[An alternative is to make the reference to U482 absolute, but the F2-way is a bit simpler.]

<SUP>edit</SUP> Um, or you could just put =A1 in the other cell. doh! <SUB>/edit</SUB>

Thank you Greg. I would like the new destination cell to either be:

a) Empty (with no data, formulas, or references in it) so I can manually enter data into it, or
b) Automatically populated with the result of the value_if_true result.

Is this possible?

 

[Weaver]

The only way I know of to put values into pristine, formula-less cells is through VB.

If you want to manually enter data, why have the result of cell A1 in it?

 

[Greg Truby]

Can you explain why you can't just put =A1 in the cell we're talking about? As excelr8r has indicated, the only way to transfer a hard coded value from A1 to cell ?? would be using VBA (probably using the worksheet's calc event handler). However I always favor a non-VBA-based solution over a VBA-based solution when possible simply because you avoid all of the security issues.

 

[Paul Herron]

Thanks excelR8R and Greg.

I would manually enter data into the destination cell (the cell with nothing in it) only if the value_if_false outcome occurred.

In other words, if the IF statement were in cell A1 and the value_if_true outcome occurred, then 0.001 would display in the destination cell.

However, it the value_if_false outcome occured, then no value would display in the destination cell (the cell with nothing in it). In this case I would then enter data manually in the destination cell.

 

[Greg Truby]

In that case you're going to need VBA. Because even if we put a formula in there to transfer when TRUE, you're going to nuke the formula when you type in for false. Care to take a crack at coding this yourself and then asking us for help when you need some?

 

[Paul Herron]

I'd be starting from scratch in terms of VB coding (and would like the challenge) but unfortunately I don't have the time to learn how to do this now. I'm going to go back to my spreadsheet, rethink my design, and coe up with a workaround.

Greg & excelR8R, my thanks to you both for your excellent help and thoughts.

Paul

 

[Greg Truby]

Sometimes dropping back and rethinking design is just what the doctor ordered. If you find yourself doing some things in cell formulae that seem a bit non-intuitive, here's a tip:


You can write yourself a note in a cell's formula by putting your comment inside the N() function (no you can't make part of a formula's text blue in Excel, I'm just doing that here for emphasis).

• =IF(«complex conditional equation»,A2,«something from left field») + N("This is a note on why I did this so that when I look at it in a year I'll have a clue.")

 

[Paul Herron]

Wow, that a great tip Greg -- much appreciated and again, thank you.