if and then formula

[tiggatattoo]

I am looking to see if someone can help me with a formula string that may work. I have one but it doesn't seem to work.

This is what I am looking to do--- the formula starts in (raw data sheet) cell c21

if (raw data sheet) cell c4 = (cost info sheet) cell a2 and if (raw data sheet) cell d4 = (cost info sheet) cell c2 then (cost info sheet) cell f2 if not then if (raw data sheet) cell c4 = (cost info sheet) cell a3 and if (raw data sheet) cell d4 = (cost info sheet) cell c3 then (cost info sheet) cell f3 if not then if (raw data sheet) cell c4 = (cost info sheet) cell a4 and if (raw data sheet) cell d4 = (cost info sheet) cell c4 then (cost info sheet) cell f4 the answer to be placed in (raw data sheet) cell c21

help! Here is the original formula, but it is not working in all the cells.

=AK30='C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Cost Info'!$B$4 (VLOOKUP('C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Raw Data'!BA30,'C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Cost Info'!$F$4:$J$9,5,FALSE)) AK30='C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Cost Info'!$D$4 (VLOOKUP('C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Raw Data'!BA30,'C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Cost Info'!$F$4:$J$9,5,FALSE)) AK30='C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Cost Info'!$B$11 (VLOOKUP('C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Raw Data'!BA30,'C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Cost Info'!$F$11:$J$16,5,FALSE)) AK30='C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Cost Info'!$B$18 'C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Raw Data'!BA30

[Allllen]

1 open the file with the 2 sheets in it. If they are in different workbooks,
the esiest thing is to copy one of them into the same WB as the other
2 rename the two sheets "cost info sheet" and "raw data sheet"
3 bang this formula in raw data cell c21

=IF(AND(C4='cost info sheet'!A2,D4='cost info sheet'!C2),'cost info
sheet'!F2,IF(AND(C4='cost info sheet'!A3,D4='cost info sheet'!C3),'cost info
sheet'!F3,IF(AND(C4='cost info sheet'!A4,D4='cost info sheet'!C4),F4,"no
matches found")))

4 rename the sheets back how they were before

5 reply here if it doesn't work

6 if it works, please give me a green tick because this took ages
--
Allllen


"tiggatattoo" wrote:

>
> I am looking to see if someone can help me with a formula string that
> may work. I have one but it doesn't seem to work.
>
> This is what I am looking to do--- the formula starts in (raw data
> sheet) cell c21
>
> if (raw data sheet) cell c4 = (cost info sheet) cell a2 *_and_* if (raw
> data sheet) cell d4 = (cost info sheet) cell c2 _*then*_ (cost info
> sheet) cell f2 _*if_not_then*_ if (raw data sheet) cell c4 = (cost info
> sheet) cell a3 *_and_* if (raw data sheet) cell d4 = (cost info sheet)
> cell c3 *_then_* (cost info sheet) cell f3 _*if_not_then*_ if (raw data
> sheet) cell c4 = (cost info sheet) cell a4 *_and_* if (raw data sheet)
> cell d4 = (cost info sheet) cell c4 _*then_*_(cost info sheet) cell f4
> *_the_answer_to_be_placed_in_(raw_data_sheet)_cell_c21_*
>
> help! Here is the original formula, but it is not working in all the
> cells.
>
> =AK30='C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce
> Allocation.xls]Cost Info'!$B$4 (VLOOKUP('C:\Documents and
> Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Raw
> Data'!BA30,'C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce
> Allocation.xls]Cost Info'!$F$4:$J$9,5,FALSE)) AK30='C:\Documents and
> Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Cost Info'!$D$4
> (VLOOKUP('C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce
> Allocation.xls]Raw Data'!BA30,'C:\Documents and
> Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Cost
> Info'!$F$4:$J$9,5,FALSE)) AK30='C:\Documents and
> Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Cost Info'!$B$11
> (VLOOKUP('C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce
> Allocation.xls]Raw Data'!BA30,'C:\Documents and
> Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Cost
> Info'!$F$11:$J$16,5,FALSE)) AK30='C:\Documents and
> Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Cost Info'!$B$18
> 'C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Raw
> Data'!BA30
>
>
> --
> tiggatattoo
> ------------------------------------------------------------------------
> tiggatattoo's Profile: http://www.excelforum.com/member.php...o&userid=35104
> View this thread: http://www.excelforum.com/showthread...hreadid=549978
>
>

[tiggatattoo]

It didn't work. Sorry,
Thank you for trying.

Here is the original string I had made. It works in most of the fields, except a few. I can't figure out why it is not working in some.

=AK28='C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Cost Info'!$B$4 (VLOOKUP('C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Raw Data'!BA28,'C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Cost Info'!$F$4:$J$9,5,FALSE)) AK28='C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Cost Info'!$D$4 (VLOOKUP('C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Raw Data'!BA28,'C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Cost Info'!$F$4:$J$9,5,FALSE)) AK28='C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Cost Info'!$B$11 (VLOOKUP('C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Raw Data'!BA28,'C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Cost Info'!$F$11:$J$16,5,FALSE)) AK28='C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Cost Info'!$B$18 'C:\Documents and Settings\CDRYDEN\Desktop\oce\[Oce Allocation.xls]Raw Data'!BA28