Match cell on sheet 1 with another sheet 2 and paste value of cell directly underneath first sheet

[thebri001]

Hi,

I am having a doosey of a time, familiar with excel and newbie to VBA. I know that the format of my first sheet is not up to recommended specs, but had to do it this way for ease of entering for the user......

They have windows 7 64 bit and are running excel 2010.

I use a macro to search 8 sheets and copy across tables with data in. It then deletes any blanks rows into a summary production sheet.

I now need to produce a client spec sheet that searches this Production sheet for terms and inputs the cell directly underneath it. For example,

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Benchtops
[/TD]
[TD]Room
[/TD]
[TD]Thickness
[/TD]
[TD]Material
[/TD]
[TD]Edge Profile
[/TD]
[TD]Colour
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]*******[/TD]
[TD]40mm[/TD]
[TD]Sandstone[/TD]
[TD]180 degree[/TD]
[TD]Beech[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Hotplate
[/TD]
[TD]Supplier
[/TD]
[TD]Range
[/TD]
[TD]Cutouts
[/TD]
[TD]Edge Finish
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]900mm[/TD]
[TD]Samsung[/TD]
[TD]Deluxe[/TD]
[TD]Drop in[/TD]
[TD]n/a[/TD]
[/TR]
[TR]
[TD]comments[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]comments[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Doors
[/TD]
[TD]Room
[/TD]
[TD]Material
[/TD]
[TD]Edge Profile
[/TD]
[TD]Colour
[/TD]
[TD]Thickness
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]*******[/TD]
[TD]2_pac[/TD]
[TD]2-Pac[/TD]
[TD]WHite[/TD]
[TD]18mm[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Open shelving
[/TD]
[TD]Supplier
[/TD]
[TD]Kicks
[/TD]
[TD]Panels
[/TD]
[TD]Servery
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]No[/TD]
[TD]xxx[/TD]
[TD]2-Pac[/TD]
[TD]2-pac[/TD]
[TD]same[/TD]
[/TR]
[TR]
[TD]comments[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]comments[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]






















Sheet always starts at A16 through to F? not more than row 200 I think.
This Summary sheet is dynamic, so cell refs can change. This is why i thought that a matching of terms would be the best way of compiling aclient spec sheet. Somthing like this format:
starting at A10 - D?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Benchtops[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Room[/TD]
[TD]*******[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Material[/TD]
[TD]Sandtone[/TD]
[TD]Colour[/TD]
[TD]Beech[/TD]
[/TR]
[TR]
[TD]Thickness[/TD]
[TD]40mm[/TD]
[TD]Edge Profile[/TD]
[TD]180 Deg[/TD]
[/TR]
[TR]
[TD]Hotplate[/TD]
[TD]900mm[/TD]
[TD]Cut-outs[/TD]
[TD]Drop in[/TD]
[/TR]
[TR]
[TD]Comments[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Comments[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Doors[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Room[/TD]
[TD]*******[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Material[/TD]
[TD]2-pac[/TD]
[TD]Colour[/TD]
[TD]White[/TD]
[/TR]
[TR]
[TD]Thickness[/TD]
[TD]18mm[/TD]
[TD]Edge Profile[/TD]
[TD]2-Pac[/TD]
[/TR]
[TR]
[TD]Kicks[/TD]
[TD]2-Pac[/TD]
[TD]Panels[/TD]
[TD]2-pac[/TD]
[/TR]
[TR]
[TD]Comments[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Comments[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I thought initially that i should transpose every 2 rows into 2 colums, then do a compare, but got very lost.........

I feel, like a heel, this being my first post, and asking for a lot ... but would be happy to help with any networking issues anyone has as this is my forte. They are a small family cabinet making business that i am trying to help, as they are good people who have held onto their employees during the GFC at great personal cost to themselves.

Any further information required is available at your request.

Most Sincerely and in anticipation,

Brian.