User Defined Function - look for specific value above current cell

[alfordtp]

I have the following Data Set:[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]1[/TD]
[TD]Brand[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]MFG[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Overivew1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Overview2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Brand[/TD]
[TD]DEF[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]MFG[/TD]
[TD]Peach[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]Overview1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]Overview2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Brand[/TD]
[TD]XYZ[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]MFG[/TD]
[TD]Grape[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]Overview1[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]Overview2[/TD]
[/TR]
</tbody>[/TABLE]

I need a user defined function that will look up the brand and place it in the blank cells. In the above example, the blank cells in rows 3 and 4 would say "Apple" for the brand. "Peach" would go in rows 7 and 8, and "Grape" would go in rows 11 and 12. The brand is not always two rows above the first overview cell, so it needs to go up until it reaches brand and save that as the value for the blank cell.

Hope I'm explaining this enough. Let me know if there are any questions. Thanks in advance.

Tim

 

[Norie]

Is MFG always in the cell next to the value you want in the empty cells?

 

[JackDanIce]

n/a

 

[alfordtp]

yes, that is true. In reference to the active cell (blank cell), the MFG is always above it. there may be other information between it though. I modified the table to show this.

[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD]1[/TD]
[TD]Brand[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]MFG[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Overivew1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Overview2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Brand[/TD]
[TD]DEF[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]MFG[/TD]
[TD]Peach[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Ordercode[/TD]
[TD]231[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]Overview1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Brand[/TD]
[TD]XYZ[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]MFG[/TD]
[TD]Grape[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]Overview1[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]Overview2[/TD]
[/TR]
</tbody>[/TABLE]


Basically I want the Brand to show up in all cells next to the "overview" cells. Since there will be multiple brands, it should reflect the brand that it first encounters going upward.