Help with Lookup / Match functions

[Guest]

I have a database prg. from which reports are export to Excel. But there is a particular report that shows code instead of actual headings. Let me give example

Database Reoprt

Depots f11001 f11010 f11015 f11049
Onitsh-dep-stock 0 0 3 27
Enug-wh1-stock 0 0 0 0
Owerri-dep-stock 0 0 0 31
P.harc-dep-stock 0 0 2 29

My Normal Report Templat

S/N Depots RKSF STM Cnct RS
1 Onitsh-dep-stock 0 0 3 2
2 Enug-wh1-stock 0 0 0
3 Owerri-dep-stock 0 0 0 3
4 P.harc-dep-stock 0 0 2 2

The codes and actual items

f11001 RKS
f11010 ST
f11015 Cnc
f11049 RS

I need a link to automate the link between the two files so I wont have to enter/replace the codes each time I run the report

Thanks in anticipation of your help

 

[Frank Kabel]

Hi
you already received some answers to this question in your old thread.
Please stay in the original thread
did the solutions work for you?? and if not what goes wrong?

 

[Aladin Akyurek]

Insert a new sheet and name it Admin.
Enter the following in, say, A2:B5 on Admin:

{"RKSF","f11001";"STM","f11010";"Cnct","f11015";"RSM","f11049"}

which is your code data with columns reversed. Select A2:B5, name the
selection CodeTable via the Name Box on the Formula Bar.

Let A1:E5 on sheet DB Report house:

{"Depots","f11001","f11010","f11015","f11049";"Onitsh-dep-stock",0,0,3,27;"E
nug-wh1-stock",0,0,0,0;"Owerri-dep-stock",0,0,0,31;"P.harc-dep-stock",0,0,2,
29}

Let A1:F5 house your Normal Report Template with A1:F1 housing:

{"S/N","Depots","RKSF","STM","Cnct","RSM"}

and A2:B5 housing:

{1,"Onitsh-dep-stock";2,"Enug-wh1-stock";3,"Owerri-dep-stock";4,"P.harc-dep-
stock"}

What you need is a formula that you can enter in C2, copy across, then down:

=VLOOKUP($B2,'DB Report'!$A:$E,MATCH(VLOOKUP(C$1,CodeTable,2,0),'DB
Report'!$A$1:$E$1,0),0)

I have a database prg. from which reports are export to Excel. But there

is a particular report that shows code instead of actual headings. Let me
give example:

Database Reoprt:

Depots f11001 f11010 f11015 f11049
Onitsh-dep-stock 0 0 3 27
Enug-wh1-stock 0 0 0 0
Owerri-dep-stock 0 0 0 31
P.harc-dep-stock 0 0 2 29

My Normal Report Template

S/N Depots RKSF STM Cnct RSM
1 Onitsh-dep-stock 0 0 3 27
2 Enug-wh1-stock 0 0 0 0
3 Owerri-dep-stock 0 0 0 31
4 P.harc-dep-stock 0 0 2 29

The codes and actual items:

f11001 RKSF
f11010 STM
f11015 Cnct
f11049 RSM

I need a link to automate the link between the two files so I wont have to

enter/replace the codes each time I run the report.