Solved: Formula help

[austenr]

Trying to put an "X" in the cell at the end of this formula. Im putting the formula in an empty cell in the first worksheet. I get FALSE in that cell.

[VBA]=IF('Input + Wksheet'!$B$17=1,'CP1'!D12="X")[/VBA]

[Bob Phillips]

That will happen when B17 isn't 1, you need to have a non-condition value, such as

=If( 'Input + Wksheet'!$B$17=1,'CP1'!D12="X","")

[austenr]

Bob,

There is a 1 in the cell. Its the way that the cell is formatted i think. It is a custom format that for what ever reason is something like GENERAL_. So the cell does indeed have a 1 in it and appears to have a space after it.

[Bob Phillips]

See if this flushes it out Austen

=If(TRIM('Input + Wksheet'!$B$17)="1",'CP1'!D12="X","")

[austenr]

Well that returns FALSE. I am attaching a sample WB with the same formatting as the original. The formula is in Sheet3 cell A1. Maybe I am missing something here.

[GTO]

Greetings Austen,

Trying to put an "X" in the cell at the end of this formula...

I may be mis-reading, but if by the above, you mean that an "X" should get plunked into cell D12 on sheet CP1, that will not happen AFAIK at least.

With the formula as written, upon the IF test passing, the result (TRUE/FALSE) of whether or not there is currently an "X" in CP1 D12 is what is being returned.

Hope I'm not just mis-reading

Mark

[austenr]

Mark,

Thats what I need to happen. Is there a way to do it with a formula? No offense Bob

[GTO]

I am afraid not, as a formula cannot change another cell. By that, I mean that while of course the result of a formula may effect the return of a formula in another cell, a formula cannot 'write' a value to another cell.

I take it that we cannot just put the formula in "CP1" D12?

[Bob Phillips]

Didn't even spot that. The only way that happens with a formula is if you pt the formula D12 of that sheet.

[austenr]

So how would i do that?

[austenr]

ok i put the formula in D12 of the CP1 sheet and I get a circular reference.

[GTO]

Now we just need to return an "X" or nothing I believe.

=IF(TRIM('Input + Wksheet'!$A$1)="1","X","")

Does that do what you want?

Mark

[austenr]

Yep that got it. Thanks Bob and Mark.

[austenr]

OK This just got a lot more complicated and need some help/advice. The above formula still will work, however, I just found out that there are some additional things I need to associate with it like VLOOKUP and some calculations.

Heres what I need to do. Not sure if I can do it with a formula but would like to. I have a cell that holds the number of years a person has worked at the company. I need to add a VLOOKUP with that valuse to find the total cost of their premium in another sheet. Once I find that value I need to divide it by 12 to get the monthly cost and then place that value in a cell on another worksheet.

So the original formula is :

[VBA]

=IF(TRIM('Input + Wksheet'!$A$1)="1","X","")

[/VBA]

Then if it is "X", do a VLOOKUP using the value in D16 and find the value in column B of Sheet 3. Then retrieve the value in Column H and put that value in another cell in another sheet.

If you can do it with a formula I know its way beyond what I can come up with.

[austenr]

Here is what I have so far but it doesnt work:

[vba]=IF(TRIM('Input + Wksheet'!$B$13)="1",HLOOKUP('CP1'!$D$12,PPO!$H$10:$H$40,6,PPO!H15))[/vba]

Also attached a sample WB. The result would go in CP! sheet E25.

[Bob Phillips]

How about this AUsten

=IF(TRIM('Input + Wksheet'!$B$13)="1",VLOOKUP('CP1'!$D$12,PPO!$B$10:$H$40,7,FALSE))

[austenr]

Hi Bob, got a #REF! error.

[austenr]

Never mind I got it.

[gcomyn]

hey... I was looking at this, and I got an #N/A error...

I noticed that the cell that is the Lookup_value is empty.. and if it was filled, it would probably be with an X...

The value for that function should be between 10 and 40...

I see.. .instead of 'CP1'!$D$12, it should be 'Input + Wksheet'!$D$12.... since that is the years of service you are looking up....

GComyn

[austenr]

I know I fixed it. Thanks for pointing it out. On an unrelated matter but still related to this problem, whay would you get 0 if you put this in a cell and there was a good number in it (it being $h$42 on the sheet referenced.:

[VBA]='CP1'!$H$42[/VBA]