Thread: Specifying row number in formula

Hello all,

Please help~

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

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 substitue with 2"

I wish to have a way effectively changing the row number for each formula.

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

Arent all data of same cargo in same row?

And isn't Cargo A in 1 row not second?

The simplest thing to avoid repetitive calcs is to conduct a single match to establish appropriate row from which data is to be retrieved, eg:

Sheet2!B1:
=MATCH(A1,Sheet1!A:A,0)
where A1 holds "Cargo A"

Continuing with assumption that the above resides in B1 on sheet2 and data1 (Sheet1!B2) is to reside in A2 on Sheet2, data2 (Sheet1!C2) in A3 etc then:

Sheet2!A2
=INDEX(Sheet1!$A:$AZ,$B$1,ROWS(A$1:A2))
copied down as required

Note: in above I've assumed as outlined that Data1 is in Column B on Sheet1 (rather than Col A) given we assume "Cargo A" is in fact the value listed in Column A (per your example)

If needed - post a sample file to better illustrate setup.

Kelvin, see attached

In short you need to get some consistency between values used in A column on AGENCY sheet and those headers used on Classification sheet - to avoid repetitive match calc see use of D1 (exclude from your print range as nec.)

If you really need to alter Col A values such that they do not match headers on Classification sheet then I would suggest you consider using Custom Formatting to display & print different text to underlying values.

Hi!

The 2nd possibility. i'm not sure is this correct but trying on sheet provided by Dk i have found the same result. Remove blank rows from a sheet2

In D5 and copy down

=VLOOKUP(D$3,Specification!$A$2:$Q$39,ROW()-1,FALSE)