Transfer specific data from 1 worksheet to another using conditions

**Chrisaaron** · 11-23-2010, 04:18 AM

Hi all,

I work in the transport industry, and am trying to create a database of movements carried out by specific vehicles. What I need to do is transfer all data in the related row to the relevant sheet, if the column with the vehicle registration on sheet 1 matches the sheet name. I have alread inserted formulaes to do this, but it is very tedious and not very neat (leaving spaces between each movement). I also find that if I insert or delete rows on sheet 1 all the formulaes on the related sheets are changed.

Please help!

**teylyn** · 11-23-2010, 05:54 AM

Hello Chrisaaron,

I struggle to see what you want to achieve. You've dumped a workbook on us, but there is no explanation in your narrative as to what is what and where it is supposed to go.

There is no "Sheet1" in your file.
It is not clear at all what your source data is and what your desired output is. You'll need to explain what the outcome is and what rules lead to it.

Please help us help you. We can't read your mind.

cheers,

**Chrisaaron** · 11-23-2010, 07:01 AM

Hi Teylyn,

My apologies for not being to clear. If you take a look at the attached workbook, all data is stored on "Plan". What I would like to do is create a formula to to automatically extract data from "Plan" to a specific worksheet, eg "ND647020", only if the data in "Plan!H" matches the last three digits of the vehicle registration. I have used the following formula:

=IF(Plan!H15="017",Plan!F15,"")

But, as I have said in my previous post, this is not very affective, because I cannot add or delete rows in"Plan" with changing the ranges in the formulaes. I also have to continually delete rows on workbooks "ND647020", "ND371504" and "ND647017".

I hope this clarifies, and appreciate your help.

**teylyn** · 11-24-2010, 03:40 AM

Chrisaaron,

in the attached file, I've created a simple table with direct references to the plan sheet. In column A I placed the Allocation data. Then I turned on Autofilter and now can filter the data by the values in column A.

Once filtered, you can enter your data for the other columns.

This way you don't need a separate sheet for each allocation number and you don't have to painstakingly adjust your formulas.

Is that something that would work for you?