Formula & moving data

[Quinton]

Hello my friends (I'm using 2007 version)

I currently import data into a sheet. It always has the same columns etc. However the data grows every time I import i.e. more lines appear and the data is never on the same line.

Below is an example of the data:

A B C D
Site Name Plot Planned Date Actual Date
The Gables Plot 1 16.02.11 13.03.11
The Gables Plot 2 19.02.11 18.03.11
And so on!!

I would like to - In another sheet - do the following:

If a row in column "A" has "the Gables" & the same row in the next column "B" has "plot 1" then show the data in column "C".

All I want to do is show 13.03.11 in a cell in another sheet.

Many thanks in advance!
<!-- / message -->

 

[dvananthan]

hi

use filter or better still advance filter

regards

ananth

 

[Quinton]

Thanks for your help, sorry but could you please expand a little??

 

[dvananthan]

hi

if you data is continuous then choose the heading row and click on data /filter /auto filter

drop drown arrows will appear on each column headings
first choose "the gables". it will show all entries with the gables then clock drop down on plot no. and choose plot 1 then you have all the gables which have plot 1

regards

a nanth

 

[Quinton]

Thanks, but this is not what I want.

I will try to explain again - Sorry:

I want to get data from 1 sheet into another & I can't simpy use the = command because the next time I import (or copy & paste) the data into a sheet it will have moved onto a different line as the data grows and changes every month.

Therefore I need a more advanced formula than just =. It needs to first identify the data & then show it.

Does this help?

Cheers,

 

[dvananthan]

hi

one clafication, does The gables and plot1 come only once in your record? meaning is the gables + plot1 unique? If yes then use index-match function then it will select the record where site is the gables and plot is plot1

regards

ananth

 

[Quinton]

Thanks for your help I really appreciate it!!

No they Dont. the Gables appears about 6 times & plot 1 appears numerous times.

But what does happen is that when the data is copied across The Gables & plot 1 will always be together on the same row. The problem I have is that row number will change every month.

I think I need an "IF" formula that would do something like this:

IF "The Gables" appears in column "A" & IF "Plot 1" Appears in Column 2 then SHOW the data for Column "C" - bearing in mind that all 3 sets of data will always be on the same row as each other.

Does this help?

Cheers,

 

[Quinton]

Sorry had a better idea for the formula (or better way of explaining it):

IF "The Gables" Appears anywhere in "Column 1" & IF "Plot 1" appears anywhere in "Column 2" (but they would always be on the same line together) then show the data that sits on the same line in "Column 3"

Jeeze this is doing my head in!

Cheers,

 

[imrantippu]

Hi,

can't get you exactly but i have come accross such situation almost daily. Concatente Coulum 1 & coulm2 can help

an Hlookup can help as well. please share at least to examples of the data and i will fix it

 

[dvananthan]

hi

filter or advance filter is the best option
do you know how to use the filter option?

regards

ananth