I often need to compare 2 values of the same material and I thought that a
reference to one of them would help me a lot. Well, the value I have chosen
to refer to is corresponding to a certain material from the spreadsheet (a
certain row than) and keeps changing its place in the column in accordance
with how many materials have been added lately. Well, I wish to make a
reference to the cell like this:
i.e.
in B2 I have the name of material="BOLTS"...................in G2 I have the
value that interests me =400
I want to add or substract (doesn't matter) this value (without knowing that
it still is in G2, maybe this time will be in G6, having another 4 materials
added above "BOLTS") to another cell, from another report that correspond to
the "BOLTS" name somehow. I know I have to make a reference to the name of
material (the text, the only one which doesn't change), but I couldn't find a
direct way.
I'm not sure I succeeded to make myself very clear, but any idea would be
appreciated for sure!

Try:

=vlookup(b2,C1:D40,2,0)

Substitute your range in place of C1:D40. You will also need to change the
2 to pick the value from the correct column.

Bear in mind that in your example, "BOLTS" will need to match exactly the
"BOLTS" in the range you are searching for.

HTH

Ian



"Liliana" wrote:

I often need to compare 2 values of the same material and I thought that a
reference to one of them would help me a lot. Well, the value I have chosen
to refer to is corresponding to a certain material from the spreadsheet (a
certain row than) and keeps changing its place in the column in accordance
with how many materials have been added lately. Well, I wish to make a
reference to the cell like this:
i.e.
in B2 I have the name of material="BOLTS"...................in G2 I have the
value that interests me =400
I want to add or substract (doesn't matter) this value (without knowing that
it still is in G2, maybe this time will be in G6, having another 4 materials
added above "BOLTS") to another cell, from another report that correspond to
the "BOLTS" name somehow. I know I have to make a reference to the name of
material (the text, the only one which doesn't change), but I couldn't find a
direct way.
I'm not sure I succeeded to make myself very clear, but any idea would be
appreciated for sure!

Well, it does match exactly! Thank you so much! I'll try it, but if I
encounter more troubles, can I ask you for more, please?

"Ian P" wrote:

Try:

=vlookup(b2,C1:D40,2,0)

Substitute your range in place of C1:D40. You will also need to change the
2 to pick the value from the correct column.

Bear in mind that in your example, "BOLTS" will need to match exactly the
"BOLTS" in the range you are searching for.

HTH

Ian



"Liliana" wrote:

I often need to compare 2 values of the same material and I thought that a
reference to one of them would help me a lot. Well, the value I have chosen
to refer to is corresponding to a certain material from the spreadsheet (a
certain row than) and keeps changing its place in the column in accordance
with how many materials have been added lately. Well, I wish to make a
reference to the cell like this:
i.e.
in B2 I have the name of material="BOLTS"...................in G2 I have the
value that interests me =400
I want to add or substract (doesn't matter) this value (without knowing that
it still is in G2, maybe this time will be in G6, having another 4 materials
added above "BOLTS") to another cell, from another report that correspond to
the "BOLTS" name somehow. I know I have to make a reference to the name of
material (the text, the only one which doesn't change), but I couldn't find a
direct way.
I'm not sure I succeeded to make myself very clear, but any idea would be
appreciated for sure!

I have an additional question thow: what about if the "BOLTS" won't be always
in the cell B2, cause THAT is my problem, that the corresponding rows are
changing, I mean today "BOLTS" is in B2, tomorrow could be in B4 or B1,
according with how many materials were added or deleted. Do you have any idea
how can I make a reference related with the name of the material?

Many thanks in advance!!! I really appreciate your help!

"Ian P" wrote:

Try:

=vlookup(b2,C1:D40,2,0)

Substitute your range in place of C1:D40. You will also need to change the
2 to pick the value from the correct column.

Bear in mind that in your example, "BOLTS" will need to match exactly the
"BOLTS" in the range you are searching for.

HTH

Ian



"Liliana" wrote:

I often need to compare 2 values of the same material and I thought that a
reference to one of them would help me a lot. Well, the value I have chosen
to refer to is corresponding to a certain material from the spreadsheet (a
certain row than) and keeps changing its place in the column in accordance
with how many materials have been added lately. Well, I wish to make a
reference to the cell like this:
i.e.
in B2 I have the name of material="BOLTS"...................in G2 I have the
value that interests me =400
I want to add or substract (doesn't matter) this value (without knowing that
it still is in G2, maybe this time will be in G6, having another 4 materials
added above "BOLTS") to another cell, from another report that correspond to
the "BOLTS" name somehow. I know I have to make a reference to the name of
material (the text, the only one which doesn't change), but I couldn't find a
direct way.
I'm not sure I succeeded to make myself very clear, but any idea would be
appreciated for sure!

Another option is:

=INDEX(B:C,MATCH(A1,B:B,0),2)

This assumes the BOLTS descriptor is in column B and the value you want to
return is in column C. If this is different you will have to change the C
and also the final 2 which is telling it where to choose the balue from.

This should work ok even if you delete/add rows - as long as you don't
delete the rows with the search formulas.

HTH

Ian

"Liliana" wrote:

I have an additional question thow: what about if the "BOLTS" won't be always
in the cell B2, cause THAT is my problem, that the corresponding rows are
changing, I mean today "BOLTS" is in B2, tomorrow could be in B4 or B1,
according with how many materials were added or deleted. Do you have any idea
how can I make a reference related with the name of the material?

Many thanks in advance!!! I really appreciate your help!

"Ian P" wrote:

Try:

=vlookup(b2,C1:D40,2,0)

Substitute your range in place of C1:D40. You will also need to change the
2 to pick the value from the correct column.

Bear in mind that in your example, "BOLTS" will need to match exactly the
"BOLTS" in the range you are searching for.

HTH

Ian



"Liliana" wrote:

I often need to compare 2 values of the same material and I thought that a
reference to one of them would help me a lot. Well, the value I have chosen
to refer to is corresponding to a certain material from the spreadsheet (a
certain row than) and keeps changing its place in the column in accordance
with how many materials have been added lately. Well, I wish to make a
reference to the cell like this:
i.e.
in B2 I have the name of material="BOLTS"...................in G2 I have the
value that interests me =400
I want to add or substract (doesn't matter) this value (without knowing that
it still is in G2, maybe this time will be in G6, having another 4 materials
added above "BOLTS") to another cell, from another report that correspond to
the "BOLTS" name somehow. I know I have to make a reference to the name of
material (the text, the only one which doesn't change), but I couldn't find a
direct way.
I'm not sure I succeeded to make myself very clear, but any idea would be
appreciated for sure!

Hi Ian,

May I get some extrahelp in this issue, please? I mean that YES, the index
works pretty fine but in the same spreadsheet and I can move rows up and
down, add or delete rows without breaking the references.

But this is not exactly what I need. I'll give you an example, which should
light you up a bit on my problem:

1. I'm working with references from one workbook to another, and what I need
looks like this:

Book_mat.xls Book_item.xls
A C A AN
2 "BOLTS" 55 2 "A200" REF# to Cx where
find "A200"
3 "A200" 44 3 "A205" REF# to Cx where
find "A205"
4 "A205" 33 4 "BOLTS" REF# to Cx where
find "BOLTS"

In the Book_mat the order of the list vary, any material can be up or down
next time.

I should get (I need to) in AN the values from Cx in this
order:[Book_item]AN2=33(from [Book_mat]A3, in the same manner:AN3=55 and
AN4=44.

I used, at first, the usual reference to a cell (in this case,Cx) but it's
not working when I am updating the Book_mat.xls and find out that I have in
stock NEW materials, which changes me the corresponding row for the specific
material (i.e."BOLTS" won't be in A2, it will be in A5 next time).
I searched all over the INDEX function description and it says nowhere
anything about a certain text from another workbook. I may be wrong, I'm not
that good (yet)! :) and I would appreciate any suggestion you or someone else
may have.

Liliana

"Ian P" wrote:

Another option is:

=INDEX(B:C,MATCH(A1,B:B,0),2)

This assumes the BOLTS descriptor is in column B and the value you want to
return is in column C. If this is different you will have to change the C
and also the final 2 which is telling it where to choose the balue from.

This should work ok even if you delete/add rows - as long as you don't
delete the rows with the search formulas.

HTH

Ian

"Liliana" wrote:

I have an additional question thow: what about if the "BOLTS" won't be always
in the cell B2, cause THAT is my problem, that the corresponding rows are
changing, I mean today "BOLTS" is in B2, tomorrow could be in B4 or B1,
according with how many materials were added or deleted. Do you have any idea
how can I make a reference related with the name of the material?

Many thanks in advance!!! I really appreciate your help!

"Ian P" wrote:

Try:

=vlookup(b2,C1:D40,2,0)

Substitute your range in place of C1:D40. You will also need to change the
2 to pick the value from the correct column.

Bear in mind that in your example, "BOLTS" will need to match exactly the
"BOLTS" in the range you are searching for.

HTH

Ian



"Liliana" wrote:

I often need to compare 2 values of the same material and I thought that a
reference to one of them would help me a lot. Well, the value I have chosen
to refer to is corresponding to a certain material from the spreadsheet (a
certain row than) and keeps changing its place in the column in accordance
with how many materials have been added lately. Well, I wish to make a
reference to the cell like this:
i.e.
in B2 I have the name of material="BOLTS"...................in G2 I have the
value that interests me =400
I want to add or substract (doesn't matter) this value (without knowing that
it still is in G2, maybe this time will be in G6, having another 4 materials
added above "BOLTS") to another cell, from another report that correspond to
the "BOLTS" name somehow. I know I have to make a reference to the name of
material (the text, the only one which doesn't change), but I couldn't find a
direct way.
I'm not sure I succeeded to make myself very clear, but any idea would be
appreciated for sure!

I tried to use vlookup combined with indirect function, but I didn't make it,
unfortunately. Maybe it gives you some idea...

"Ian P" wrote:

Another option is:

=INDEX(B:C,MATCH(A1,B:B,0),2)

This assumes the BOLTS descriptor is in column B and the value you want to
return is in column C. If this is different you will have to change the C
and also the final 2 which is telling it where to choose the balue from.

This should work ok even if you delete/add rows - as long as you don't
delete the rows with the search formulas.

HTH

Ian

"Liliana" wrote:

I have an additional question thow: what about if the "BOLTS" won't be always
in the cell B2, cause THAT is my problem, that the corresponding rows are
changing, I mean today "BOLTS" is in B2, tomorrow could be in B4 or B1,
according with how many materials were added or deleted. Do you have any idea
how can I make a reference related with the name of the material?

Many thanks in advance!!! I really appreciate your help!

"Ian P" wrote:

Try:

=vlookup(b2,C1:D40,2,0)

Substitute your range in place of C1:D40. You will also need to change the
2 to pick the value from the correct column.

Bear in mind that in your example, "BOLTS" will need to match exactly the
"BOLTS" in the range you are searching for.

HTH

Ian



"Liliana" wrote:

I often need to compare 2 values of the same material and I thought that a
reference to one of them would help me a lot. Well, the value I have chosen
to refer to is corresponding to a certain material from the spreadsheet (a
certain row than) and keeps changing its place in the column in accordance
with how many materials have been added lately. Well, I wish to make a
reference to the cell like this:
i.e.
in B2 I have the name of material="BOLTS"...................in G2 I have the
value that interests me =400
I want to add or substract (doesn't matter) this value (without knowing that
it still is in G2, maybe this time will be in G6, having another 4 materials
added above "BOLTS") to another cell, from another report that correspond to
the "BOLTS" name somehow. I know I have to make a reference to the name of
material (the text, the only one which doesn't change), but I couldn't find a
direct way.
I'm not sure I succeeded to make myself very clear, but any idea would be
appreciated for sure!