Specifying row number in formula

[lait0002]

Hello all,

I want to do as the following:

In sheet 1 I have all the data needed for sheet 2, organizing in row, which means for a particular cargo, its details will be all having same row number, different column alphabet.

Cargo A data 1 data 2 data 3 ....... and so on
Cargo B data 1 data 2 data 3 ....... and so on

In sheet 2, I have a form in which I wish to have it auto fill in the cargo particular by inserting in just the row number for the cargo in sheet 1.

Cargo A

data 1 =Sheet1!A#

data 2 =Sheet1!B#

data 3 =Sheet1!C#

"Cargo A is row 2, then # should be substitute with 2"


Hope this is not a confusing explanation for you all... Please HELP ME!

 

[Andrew Poulsom]

Welcome to the Board.

If A1 contains the cargo to look up:

=INDEX(Sheet1!A:A,MATCH(A1,Sheet1!A:A,FALSE))

=INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,FALSE))

=INDEX(Sheet1!C:C,MATCH(A1,Sheet1!A:A,FALSE))

 

[lait0002]

Hi Andrew,

Thanks for your help. I don't quite get it.

Anyway, how should I do to attach the excel file for your reference?

Thank you for your help.


Best Regards,
Kelvin

 

[Andrew Poulsom]

Sorry, what don't you get?

 

[lait0002]

Hi Andrew,


Can you explain the meaning of the equation that you have given to me?

Vessel Type IMO Call Sign Flag
SHIP 1 AHTS 9487201 9VLG5 SINGAPORE
SHIP 2 AHTS 9544669 9V8231 SINGAPORE
SHIP 3 PSV 9482469 9VHM5 SINGAPORE
SHIP 4 PSV 9482457 C6XD4 BAHAMAS

Above are parts of my data in sheet 1

I would like to do the following in sheet 2 for the rest of my ships in charge.
I need a formula that everytime I select a ship, it could help me retrieve the information in sheet 1 and fill in accordingly as below:

Ship Particular of* SHIP 1

IMO No.: 9487201
CALL SIGN: 9VLG5
FLAG: SINGAPORE


Hope this is not confusing.

 

[Andrew Poulsom]

In this formula:

=INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,FALSE))

MATCH returns the number of the row containing the value in A1. That row number is passed to the INDEX function which returns the value in column B.

If this data is in A1:E5 on Sheet1:

<TABLE style="WIDTH: 216pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=288 border=0 x:str><COLGROUP><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 31pt; mso-width-source: userset; mso-width-alt: 1499" width=41><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 36pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=48 height=17>Vessel</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 31pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=41>Type</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 42pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=56>IMO</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 44pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=59>Call Sign</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 63pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=84>Flag</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>SHIP 1</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">AHTS</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>9487201</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">9VLG5</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">SINGAPORE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>SHIP 2</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">AHTS</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>9544669</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">9V8231</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">SINGAPORE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>SHIP 3</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">PSV</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>9482469</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">9VHM5</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">SINGAPORE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>SHIP 4</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">PSV</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>9482457</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">C6XD4</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">BAHAMAS</TD></TR></TBODY></TABLE>

and B1 on your active sheet contains SHIP 1, the following formulas will return IMO, CALL SIGN and FLAG:

=INDEX(Sheet1!C:C,MATCH(B1,Sheet1!A:A,FALSE))

=INDEX(Sheet1!D:D,MATCH(B1,Sheet1!A:A,FALSE))

=INDEX(Sheet1!E:E,MATCH(B1,Sheet1!A:A,FALSE))

 

[lait0002]

Hey Andrew,

Thanks for your help. I need some time to digest it.

Anyway, Thank you!


Best Regards,
Kelvin