if and then formula

T

tiggatattoo

I am looking to see if someone can help me with a formula string tha
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 dat
sheet) cell c21

if (raw data sheet) cell c4 = (cost info sheet) cell a2 *_and_* if (ra
data sheet) cell d4 = (cost info sheet) cell c2 _*then*_ (cost inf
sheet) cell f2 _*if_not_then*_ if (raw data sheet) cell c4 = (cost inf
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 dat
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 th
cells.

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

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 said:
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
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top