PDA

View Full Version : Solved: Formula help



austenr
07-16-2010, 01:00 PM
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.

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

Bob Phillips
07-16-2010, 01:30 PM
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
07-17-2010, 08:09 AM
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
07-17-2010, 08:50 AM
See if this flushes it out Austen

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

austenr
07-17-2010, 11:46 AM
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
07-17-2010, 12:51 PM
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:dunno

Mark

austenr
07-17-2010, 12:55 PM
Mark,

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

GTO
07-17-2010, 01:04 PM
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
07-17-2010, 01:05 PM
Didn't even spot that. The only way that happens with a formula is if you pt the formula D12 of that sheet.

austenr
07-17-2010, 01:19 PM
So how would i do that?

austenr
07-17-2010, 01:38 PM
ok i put the formula in D12 of the CP1 sheet and I get a circular reference.

GTO
07-17-2010, 02:20 PM
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
07-18-2010, 07:50 AM
Yep that got it. Thanks Bob and Mark. :thumb

austenr
07-19-2010, 12:20 PM
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 :



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


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
07-19-2010, 12:59 PM
Here is what I have so far but it doesnt work:

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

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

Bob Phillips
07-19-2010, 01:15 PM
How about this AUsten

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

austenr
07-19-2010, 01:17 PM
Hi Bob, got a #REF! error.

austenr
07-19-2010, 01:31 PM
Never mind I got it.

gcomyn
07-19-2010, 01:32 PM
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
:beerchug:

austenr
07-19-2010, 01:48 PM
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.:

='CP1'!$H$42

EDGE
07-19-2010, 02:20 PM
From the looks of it the cell (CP1!H42 ) you are referencing wouldn't contain any data. Make sure you are referencing the correct cell.

gcomyn
07-19-2010, 02:27 PM
uhm... In the workbook that you sent out above, the cell 'CP1'!$H$42 is empty, and doesn't look like it should have any numbers in it.... so I'm not sure what you are asking.

GTO
07-20-2010, 02:19 AM
Edit/Delete... (Missed that there was a second page. Doh!)

austenr
07-20-2010, 07:03 AM
ok i got it all working except that I now need a way to check to see if a person is a certain age. I have that calculation in a cell on a worksheet.

So if we use this formula:

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

and the age of the employee is < 65 its ok the way it is. However if the employee is over 65 then I need to use column 14 in the VLOOKUP. Whats the best approach?

gcomyn
07-20-2010, 07:25 AM
Ok... I would try this:


=IF(TRIM(MEDCVG)="1",VLOOKUP(YearsOfService,Indemnity,IF(AGE<=65,7,14),FALSE),0)/12


Where:
MEDCVG = 'Input + Wksheet'!$B$13
YearsOfService='Input + Wksheet'!$D$12
Indemnity=Indemnity!$B$10:$Q$40
AGE='Input + Wksheet'!$K$7

right now, the table that you are using is only 7 column wide... so you would get an error if you just put 14 as the column reference... yo have to make the table large enough to use. So I just put named ranges in there so they can be changed as needed, without having to change all of the formulas that are affected.

GComyn

austenr
07-20-2010, 07:32 AM
Great Ill give it a try. Nice explanation BTW.

austenr
07-20-2010, 09:15 AM
If you use named ranges in formulas and the named range is on another sheet, do you have to specify the sheet of the range in the formula?

gcomyn
07-20-2010, 09:17 AM
only if you have the same named range on multiple sheets. If there is only one name used in the entire workbook, then no, you don't.

GComyn

austenr
07-20-2010, 09:59 AM
Well I got it to work but the strangest thing. When I copied and pasted your formula using named ranges and created my named ranges to match yours as you instructed, when I pasted the formula into the destination cell, I get 0. However, if I go to the Indemnity tab and switch back to the CP tab, the correct result appears in the destination cell. Destination cell is where I pasted the formula. Weird.

austenr
07-20-2010, 10:26 AM
Got it turned on Auto Calc in Tools>Options. Problem Solved

austenr
07-21-2010, 06:36 AM
Thiought I had this figurend out except one thing. In this formula:

=VLOOKUP(YearsOfService,Indemnity,IF(Age<=65,9,15),FALSE)/12

If the individual is over 65 then I need to put "N/A" in the cell. I tried several ways but cant get it quite right.

gcomyn
07-21-2010, 07:39 AM
well.... I used 7,14... you are using 9,15.... did you change the size of the table? the table that I saw was only 14 columns wide... so if you try to point it to the 15th column, it will error on you.

[edited] oops... my bad... I just went back and looked at it, and saw that there are actually 16 columns.... So...



=If(Age>65,"N/A",VLOOKUP(YearsOfService,Indemnity,9,False)/12 )



GComyn

austenr
07-21-2010, 07:46 AM
Changed the formula, and the columns it references. The original IF was for the first "Employee only" cell. Had to change the columns as I went along to ones so it would pick up the right tier. All I want to do is find a way to replace column 15 with "N/A" as the cell value. I know that if you just replace column 15 with that the way the formula is now it will throw an error because of the division. Im using the formula in a number of places but just one place uses the IF statement because it checks to see if the employee is single. That is the only use for it (the IF statement). HTH