vlookup in to a hyper link

[VeeraSinha]

Hi,

I have 2 workbooks in the same file. WKBK1 "main" has 2 columns . col 1 is a list of part numbers and col 2 if the "totalcost" of that part number. WKBK2 has 4 columns, first col is same list of parts and col 4 if the total cost and col 2 and 3 are break up of that cost.

I need to make the total cost cell in wkbk1 as a clickable link. so the user can see the cost break up in wkbk 2 . on clicking i need the cursor to go to the matching part row and total cost col in wkbk2.
Heres what I am trying anf the resul is the total cost as a clickable link but when I click on it i get an error. "Can not open the specified file"

=HYPERLINK(VLOOKUP(A18,Details!A:P,16,FALSE))

Please please help . I have a deadline coming up.

Thanks in advance.

Veera

 

[VeeraSinha]

yes it is continuous set of data A through AE,

The format of detail tab is as follows:

<TABLE style="WIDTH: 1715pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=2285 border=0 x:str><COLGROUP><COL style="WIDTH: 137pt; mso-width-source: userset; mso-width-alt: 5856" width=183><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2464" width=77><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2592" width=81><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2464" width=77><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2048" span=12 width=64><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2464" width=77><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2592" width=81><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2464" width=77><COL style="WIDTH: 54pt" span=12 width=72><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl254 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 137pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ccffcc" width=183 height=20>DONOT DELETE ( concatenate)</TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 58pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=77 x:str="6Flash cost" x:fmla="=CONCATENATE(B2,B3)"> 6Flash cost </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 61pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=81 x:str="6# Placmnts" x:fmla="=CONCATENATE(C2,C3)"> 6# Placmnts </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 58pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=77 x:str="6Expl Flash" x:fmla="=CONCATENATE(D2,D3)"> 6Expl Flash </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:str="6Subcon APR" x:fmla="=CONCATENATE(E2,E3)"> 6Subcon APR </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:str="6Keys" x:fmla="=CONCATENATE(F2,F3)"> 6Keys </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:str="6Adapter" x:fmla="=CONCATENATE(G2,G3)"> 6Adapter </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:str="6Meter" x:fmla="=CONCATENATE(H2,H3)"> 6Meter </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:str="6Controller" x:fmla="=CONCATENATE(I2,I3)"> 6Controller </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:str="6MTS" x:fmla="=CONCATENATE(J2,J3)"> 6MTS </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:str="6MTS fulfill" x:fmla="=CONCATENATE(K2,K3)"> 6MTS fulfill </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:str="6Freight-In" x:fmla="=CONCATENATE(L2,L3)"> 6Freight-In </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:str="6Freight-Out" x:fmla="=CONCATENATE(M2,M3)"> 6Freight-Out </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:str="6Overhead" x:fmla="=CONCATENATE(N2,N3)"> 6Overhead </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:str="6Non-flash cost" x:fmla="=CONCATENATE(O2,O3)"> 6Non-flash cost </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:str="6RC JUL09" x:fmla="=CONCATENATE(P2,P3)"> 6RC JUL09 </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 58pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=77 x:str="7Flash cost" x:fmla="=CONCATENATE(Q2,Q3)"> 7Flash cost </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 61pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=81 x:str="7# Placmnts" x:fmla="=CONCATENATE(R2,R3)"> 7# Placmnts </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 58pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=77 x:str="7Expl Flash" x:fmla="=CONCATENATE(S2,S3)"> 7Expl Flash </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=72 x:str="7Subcon APR" x:fmla="=CONCATENATE(T2,T3)"> 7Subcon APR </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=72 x:str="7Keys" x:fmla="=CONCATENATE(U2,U3)"> 7Keys </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=72 x:str="7Adapter" x:fmla="=CONCATENATE(V2,V3)"> 7Adapter </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=72 x:str="7Meter" x:fmla="=CONCATENATE(W2,W3)"> 7Meter </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=72 x:str="7Controller" x:fmla="=CONCATENATE(X2,X3)"> 7Controller </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=72 x:str="7MTS" x:fmla="=CONCATENATE(Y2,Y3)"> 7MTS </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=72 x:str="7MTS fulfill" x:fmla="=CONCATENATE(Z2,Z3)"> 7MTS fulfill </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=72 x:str="7Freight-In" x:fmla="=CONCATENATE(AA2,AA3)"> 7Freight-In </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=72 x:str="7Freight-Out" x:fmla="=CONCATENATE(AB2,AB3)"> 7Freight-Out </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=72 x:str="7Overhead" x:fmla="=CONCATENATE(AC2,AC3)"> 7Overhead </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=72 x:str="7Non-flash cost" x:fmla="=CONCATENATE(AD2,AD3)"> 7Non-flash cost </TD><TD class=xl255 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=72 x:str="7RC Aug09" x:fmla="=CONCATENATE(AE2,AE3)"> 7RC Aug09 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl254 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ccffcc" height=20>Month number</TD><TD class=xl256 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 58pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=77 x:num>6</TD><TD class=xl256 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 61pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=81 x:num>6</TD><TD class=xl256 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 58pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=77 x:num>6</TD><TD class=xl256 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:num>6</TD><TD class=xl256 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:num>6</TD><TD class=xl256 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:num>6</TD><TD class=xl256 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:num>6</TD><TD class=xl256 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:num>6</TD><TD class=xl256 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:num>6</TD><TD class=xl256 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:num>6</TD><TD class=xl256 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:num>6</TD><TD class=xl256 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:num>6</TD><TD class=xl256 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:num>6</TD><TD class=xl256 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:num>6</TD><TD class=xl256 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffcc" width=64 x:num>6</TD><TD class=xl257 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 58pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffcc99" width=77 x:num>7</TD><TD class=xl257 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 61pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffcc99" width=81 x:num>7</TD><TD class=xl257 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 58pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffcc99" width=77 x:num>7</TD><TD class=xl257 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffcc99" width=72 x:num>7</TD><TD class=xl257 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffcc99" width=72 x:num>7</TD><TD class=xl257 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffcc99" width=72 x:num>7</TD><TD class=xl257 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffcc99" width=72 x:num>7</TD><TD class=xl257 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffcc99" width=72 x:num>7</TD><TD class=xl257 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffcc99" width=72 x:num>7</TD><TD class=xl257 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffcc99" width=72 x:num>7</TD><TD class=xl257 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffcc99" width=72 x:num>7</TD><TD class=xl257 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffcc99" width=72 x:num>7</TD><TD class=xl257 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffcc99" width=72 x:num>7</TD><TD class=xl257 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffcc99" width=72 x:num>7</TD><TD class=xl257 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffcc99" width=72 x:num>7</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD class=xl258 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 137pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: #ccffcc" width=183 height=22>Ref part #</TD><TD class=xl259 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=77 x:str="Flash cost"> Flash cost </TD><TD class=xl258 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 61pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=81># Placmnts</TD><TD class=xl258 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=77>Expl Flash</TD><TD class=xl260 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64 x:str="Subcon APR"> Subcon APR </TD><TD class=xl260 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64 x:str="Keys"> Keys </TD><TD class=xl260 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64 x:str="Adapter"> Adapter </TD><TD class=xl260 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64 x:str="Meter"> Meter </TD><TD class=xl260 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64 x:str="Controller"> Controller </TD><TD class=xl260 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64 x:str="MTS"> MTS </TD><TD class=xl260 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64 x:str="MTS fulfill"> MTS fulfill </TD><TD class=xl260 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64 x:str="Freight-In"> Freight-In </TD><TD class=xl260 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64 x:str="Freight-Out"> Freight-Out </TD><TD class=xl260 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64 x:str="Overhead"> Overhead </TD><TD class=xl260 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64 x:str="Non-flash cost"> Non-flash cost </TD><TD class=xl258 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64>RC JUL09</TD><TD class=xl261 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99" width=77 x:str="Flash cost"> Flash cost </TD><TD class=xl262 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 61pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99" width=81># Placmnts</TD><TD class=xl262 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99" width=77>Expl Flash</TD><TD class=xl263 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99" width=72 x:str="Subcon APR"> Subcon APR </TD><TD class=xl263 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99" width=72 x:str="Keys"> Keys </TD><TD class=xl263 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99" width=72 x:str="Adapter"> Adapter </TD><TD class=xl263 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99" width=72 x:str="Meter"> Meter </TD><TD class=xl263 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99" width=72 x:str="Controller"> Controller </TD><TD class=xl263 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99" width=72 x:str="MTS"> MTS </TD><TD class=xl263 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99" width=72 x:str="MTS fulfill"> MTS fulfill </TD><TD class=xl263 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99" width=72 x:str="Freight-In"> Freight-In </TD><TD class=xl263 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99" width=72 x:str="Freight-Out"> Freight-Out </TD><TD class=xl263 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99" width=72 x:str="Overhead"> Overhead </TD><TD class=xl263 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99" width=72 x:str="Non-flash cost"> Non-flash cost </TD><TD class=xl262 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99" width=72>RC Aug09</TD></TR></TBODY></TABLE>

Col b through P will replicate for eact month. No space as of now. I could add a blank column between months if needed.

This month number will be entered by user in Main tab. Finance cost in Main tab (our link we worked on )will show corresponding values ( month match) from details tab.

 

[Nogslaw]

Sorry - one more question. Will the Details sheet always have June/July or will there be more additions? Will there eventually be months 1-12 across the row, or will there be a limited number of months?

 

[VeeraSinha]

yes you are right. the months will be from 1 to 12.July , Aug ,sep,oct ...

 

[Nogslaw]

Okay, if you organize it so that January is at the beginning of the row, and December is at the far right, then the following will work, although it is getting rather messy:

=HYPERLINK("[Profitability Analysis 6-25-2009 veera.xls]Details!"&ADDRESS(MATCH(A16,Details!$A$1:$A$100,0),COLUMN(OFFSET(A1,,15*(C1)))),INDIRECT("Details!"&ADDRESS(MATCH(A16,Details!$A$1:$A$100,0),COLUMN(OFFSET(A1,,15*(C1))))))

You said the user would enter a month number in a cell on the main page. In the formula above, this cell is C1. Adjust that reference to your specific cell.

Since you currently have June as the first month, and July as the second month, you can test by either entering 1 for June or 2 for July.

 

[praky2t]

Hi,

It tried this formula, but still it is showing the "#REF!" error.

Please help me to fix this ASAP.

Thanks

Praky

 

[Nogslaw]

Can you be more specific about the nature of your problem? This formula is designed for very specific parameters, and I would guess that your problem stems from your workbook not having the same parameters that the other user did. Therefore, there is an invalid reference resulting in the #REF error.

Workbook Name: Profitability Analysis 6-25-2009 veera.xls
Sheet Name: Details
Specific information in specific cells, etc.

Please post more information about your question and I'll try to help. A description of what you are trying to do would be helpful, along with the formula(s) you have tried already.

 

[praky2t]

Hi,

I have 2 workbooks in the same file. WKBK1 "Main" has 4 columns . col 4 is a list of invoice values. WKBK2 has 4 columns; fourth column is same list of invoice values.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I need to make the invoice values cells in wkbk1 as a clickable link. so the user can see the same value in wkbk 2 . On clicking i need the cursor to go to the matching part row and cost col in wkbk2.

Regards

Prakash

 

[Nogslaw]

Okay, lets break this into parts because I have lots of questions.

Structure/Layout:
In one workbook, you have data in columns A:D on a worksheet called "Main".
The information in column D is an invoice amount, and you want to be able to click on the invoice amount and hyperlink to the corresponding value in a second workbook.
--Question: How is the invoice amount calculated/populated? It is manual entry or an automatic calculation from a formula? If automatic, what is your formula?
In a second workbook, you have data in columns A:D in an unidentified worksheet.
--Question: What is the name of the worksheet in this workbook.
--Question: Is there any possibility (even a very very small possibility) that two invoice values could be the same? If so, is there something else that could be used as the unique identifier (invoice number, perhaps)? If so, what columns on each sheet is this information located in?
--Question: What rows will be used in WKBK2? Will the number of rows be constantly changing, or will they be static?
--Question: What is the filepath name of WKBK2? We will most likely need to include this in the link.