Formula - Surely someone can sort this 1 out?

[Quinton]

OK can someone help as this is my 3rd Thread on this (i'm using Excel 2007).

I have 2 sheets. I copy & paste data into "Sheet 2" & I use "Sheet 1" for collating the data & generating graphs etc.

So normally I would use the = formula in "Sheet 1" to reference certain cells in "Sheet 2"

The problem I have is that the data I copy & Paste into "Sheet 2" changes everytime. 2 Examples are given below:

(ignore the dots - I just used them to get the columns to line up)

Example 1(Month 1)
.............Column 1....Column 2 Column 3
Line 1....Site Name...Plot........ Planned Date
Line 2.....The Gables...Plot 1......16.02.11
Line 3.....The Gables...Plot 2......19.02.11
Line 4.....The Gables...Plot 3......27.04.11
Line 5.....The Gables...Plot 4......16.02.11

Example 2(Month 2)
.............Column 1....Column 2 Column 3
Line 1....Site Name...Plot........ Planned Date
Line 2.....The Gables...Plot 4......12.02.11
Line 3.....The Gables...Plot 1......09.02.11
Line 4.....The Gables...Plot 2......18.04.11
Line 5.....The Gables...Plot 3......23.02.11

Normally to get Column 3, Line 4 data (27.04.11) in Example 1 above I would use =C4. However as soon as I copy & paste new data in (example 2) this result now changes to (18.04.11) as the existing data has moved from Line 4 to Line 5.

I basically need a formula that simply replaces =C4 with something like the following:

IF data within Column A =The Gables & data within Column B = Plot 3 then Please show the data in Column 3 - Whilst somehow respecting what line it in.

Good Luck & I would be eternally grateful!

Cheers,

 

[RoryA]

You could use something like:
=lookup(2, 1/((Sheet2!$A$1:$A$1000="The Gables")*(Sheet2!$B$1:$B$1000="Plot 3")),Sheet2!$C$1:$C$1000)

 

[Quinton]

Holy Moly! I Love You - In a friendly WAY!!!

Many Many Many Thanks!

Kind regards,