Indirect Vlookup with RC[-1]

[outwest]

Hi, first let me thank you all for such a knowledge packed forum.

I am trying to utilise a barcode scanner to help me return details on
machinery, but for the moment I have manually typed in the serial numbers
till I get the formulae sorted.
On sheet 1 I have all the serial numbers in column A. I then need to Vlookup
A2, then A3 etc, to return details of each machine in column B, this means
changing the formula on every line, and there is a lot of machinery.

Is there a way I can use the indirect function with Vlookup that will use the
RC parameter to make a simpler line of formula? This way I can drag 286 lines
and paste the same formula into all of them.
I want to be able to click on any row in column B and it will look across at
column A on the same row for the serial number, and then retrieve the machine
details from the list I have on sheet 2.

Here is something like I wanted, but I obviously got it all wrong.
=VLOOKUP("RC[-1]",0),INDIRECT(Sheet2!$A$1:$F$200,2,FALSE)

Hopefully I learnt something from searching all the other posts here and
wasnt too far off the money..

Note, if I use this type of format, everything works at this stage.
=IF(A4="","",VLOOKUP(A4,Sheet2!$A$1:$F$200,2,FALSE))

Many thanks for any sugestions.

 

[Arvi Laanemets]

Hi

1st, you can't use INDIRECT to valuate a formula - you can use it only to
return a text string as range. Like
=SUM(INDIRECT("Sheet1!A2:A10"))

2nd, why not simply something like this
=VÖOOKUP(A1,Sheet2!$A$1:$F$200,2,0)

Arvi Laanenemets

 

[outwest]

The data I want will be text, such as column B=colour, column C=weight etc

1st, you can't use INDIRECT to valuate a formula - you can use it only to
return a text string as range. Like
=SUM(INDIRECT("Sheet1!A2:A10"))

The line below is easier, thanks, can I simply use the RC-1 command with it
instead of changing A1 to A2 up to A286 ?

2nd, why not simply something like this
=VÖOOKUP(A1,Sheet2!$A$1:$F$200,2,0)

Thanks for such a speedy response.

 

[Guest]

i dont quite understand the problem.All you have to do is write the first row
of formula changing the column numbers as you go across,then copy the columns
down the rest of the rows required .
so a1 is=VLOOKUP(A1,Sheet2!$A$1:$F$200,1,0),B1
is=VLOOKUP(A1,Sheet2!$A$1:$F$200,2,0),C1
is=VLOOKUP(A1,Sheet2!$A$1:$F$200,3,0),and so on then copy down.....

 

[outwest]

Hi Paul,
yes, sorting the data across the columns is easy enuff.
But lets say truck 1 is in A1, then I would go for =VLOOKUP(A1,Sheet2!$A$1:$F
$200,1,0),B1
=VLOOKUP(A1,Sheet2!$A$1:$F$200,2,0),C1 etc.
but the truck in A2 or A286 will have a different combination of data, so I
would need to do =VLOOKUP(A2,Sheet2!$A$1:$F$200,1,0),B1
=VLOOKUP(A2,Sheet2!$A$1:$F$200,2,0),C1 etc. then A3, A4 all the way down to
A286.
I was hoping there was a way to increase the A1, A2 part of the formula
automatically as I entered each new line (row).
Hence I was hoping to click on column B(whatevernumber) and have it look
across at column A(whatevernumber) then go to sheet 2 to piece together the
relevant data.

=VLOOKUP(A1,Sheet2!$A$1:$F$200,1,0), works fine, its just kinda seems a bit
pre-computerised to have to change it for every row.

Its all hard to explain sorta but Im trying my best.

i dont quite understand the problem.All you have to do is write the first row
of formula changing the column numbers as you go across,then copy the columns
down the rest of the rows required .
so a1 is=VLOOKUP(A1,Sheet2!$A$1:$F$200,1,0),B1
is=VLOOKUP(A1,Sheet2!$A$1:$F$200,2,0),C1
is=VLOOKUP(A1,Sheet2!$A$1:$F$200,3,0),and so on then copy down.....

The data I want will be text, such as column B=colour, column C=weight etc

[quoted text clipped - 8 lines]

Thanks for such a speedy response.

 

[Richard Buttrey]

Hi Paul,
yes, sorting the data across the columns is easy enuff.
But lets say truck 1 is in A1, then I would go for =VLOOKUP(A1,Sheet2!$A$1:$F
$200,1,0),B1
=VLOOKUP(A1,Sheet2!$A$1:$F$200,2,0),C1 etc.
but the truck in A2 or A286 will have a different combination of data, so I
would need to do =VLOOKUP(A2,Sheet2!$A$1:$F$200,1,0),B1
=VLOOKUP(A2,Sheet2!$A$1:$F$200,2,0),C1 etc. then A3, A4 all the way down to
A286.
I was hoping there was a way to increase the A1, A2 part of the formula
automatically as I entered each new line (row).
Hence I was hoping to click on column B(whatevernumber) and have it look
across at column A(whatevernumber) then go to sheet 2 to piece together the
relevant data.

=VLOOKUP(A1,Sheet2!$A$1:$F$200,1,0), works fine, its just kinda seems a bit
pre-computerised to have to change it for every row.

Its all hard to explain sorta but Im trying my best.

I'm obviously still not quite understanding your problem.
with B1=
=VLOOKUP(A1,Sheet2!$A$1:$F$200,1,0)
when you copy this down to B2, surely you end up with
=VLOOKUP(A2,Sheet2!$A$1:$F$200,1,0)

What do you mean when you say above 'as I enter each new line (row)'?
Are you not just entering a new value in A2 and copying B1 to B2?

Rgds

i dont quite understand the problem.All you have to do is write the first row
of formula changing the column numbers as you go across,then copy the columns
down the rest of the rows required .
so a1 is=VLOOKUP(A1,Sheet2!$A$1:$F$200,1,0),B1
is=VLOOKUP(A1,Sheet2!$A$1:$F$200,2,0),C1
is=VLOOKUP(A1,Sheet2!$A$1:$F$200,3,0),and so on then copy down.....

The data I want will be text, such as column B=colour, column C=weight etc

[quoted text clipped - 8 lines]

Thanks for such a speedy response.

Richard Buttrey
__

 

[Biff]

To add to the confusion....

When you want to copy the formula across a series of columns and have the
column_num argument automatically increment:

=VLOOKUP($A1,Sheet2!$A$1:$F$200,COLUMNS($A:B),0)

Biff

Hi Paul,
yes, sorting the data across the columns is easy enuff.
But lets say truck 1 is in A1, then I would go for
=VLOOKUP(A1,Sheet2!$A$1:$F
$200,1,0),B1
=VLOOKUP(A1,Sheet2!$A$1:$F$200,2,0),C1 etc.
but the truck in A2 or A286 will have a different combination of data, so
I
would need to do =VLOOKUP(A2,Sheet2!$A$1:$F$200,1,0),B1
=VLOOKUP(A2,Sheet2!$A$1:$F$200,2,0),C1 etc. then A3, A4 all the way down
to
A286.
I was hoping there was a way to increase the A1, A2 part of the formula
automatically as I entered each new line (row).
Hence I was hoping to click on column B(whatevernumber) and have it look
across at column A(whatevernumber) then go to sheet 2 to piece together
the
relevant data.

=VLOOKUP(A1,Sheet2!$A$1:$F$200,1,0), works fine, its just kinda seems a
bit
pre-computerised to have to change it for every row.

Its all hard to explain sorta but Im trying my best.

I'm obviously still not quite understanding your problem.
with B1=
=VLOOKUP(A1,Sheet2!$A$1:$F$200,1,0)
when you copy this down to B2, surely you end up with
=VLOOKUP(A2,Sheet2!$A$1:$F$200,1,0)

What do you mean when you say above 'as I enter each new line (row)'?
Are you not just entering a new value in A2 and copying B1 to B2?

Rgds

i dont quite understand the problem.All you have to do is write the first
row
of formula changing the column numbers as you go across,then copy the
columns
down the rest of the rows required .
so a1 is=VLOOKUP(A1,Sheet2!$A$1:$F$200,1,0),B1
is=VLOOKUP(A1,Sheet2!$A$1:$F$200,2,0),C1
is=VLOOKUP(A1,Sheet2!$A$1:$F$200,3,0),and so on then copy down.....

The data I want will be text, such as column B=colour, column C=weight
etc

[quoted text clipped - 8 lines]

Thanks for such a speedy response.

Richard Buttrey
__