If I enter a value (A) I want excel to tell me a value for (A)...

Hi,

I want to create an excel spreadsheet to do the following:

Basically I want to 'size' a component by entering it's 'Full load current; (in Amps) in one cell and then in another cell I want it to display what component I should use e.g.

If I enter 1.4Am I want excel to tell me I need a 3RV1011-1BA10.

How can I achieve this? I realise that I will have to enter all the component ranges in somehow but I don't know where to start.

Set up a lookup table and use the vlookup function to access this table.
Use Excel help for this function to show how to set this up. Observe that
if, as I suspect, you are entering continuous values then you need to
determine how to convert this - vlookup can be set choose the next higher
value for the table if this is what you want. Otherwise you might want to
convert the value first.


--
Cheers
Nigel



"Ste1978" <Ste1978.24llpm_1142236501.8847@excelforum-nospam.com> wrote in
message news:Ste1978.24llpm_1142236501.8847@excelforum-nospam.com...
>
> Hi,
>
> I want to create an excel spreadsheet to do the following:
>
> Basically I want to 'size' a component by entering it's 'Full load
> current; (in Amps) in one cell and then in another cell I want it to
> display what component I should use e.g.
>
> If I enter 1.4Am I want excel to tell me I need a 3RV1011-1BA10.
>
> How can I achieve this? I realise that I will have to enter all the
> component ranges in somehow but I don't know where to start.
>
>
> --
> Ste1978
> ------------------------------------------------------------------------
> Ste1978's Profile:
http://www.excelforum.com/member.php...o&userid=32398
> View this thread: http://www.excelforum.com/showthread...hreadid=521647
>

Hi thanks for the reply,

I still don't know how to start, I have hardly any excel training at all!

I just want a step by step guide on how to begin? I read the help for VLOOKUP but I still do not understand.

Hi Ste1978,

> I just want a step by step guide on how to begin? I read the help for
> VLOOKUP but I still do not understand.

See Debra Dalgleish's VLookup tutorial at:

http://www.contextures.com/xlFunctions02.html


---
Regards,
Norman



"Ste1978" <Ste1978.24lqsz_1142243102.7492@excelforum-nospam.com> wrote in
message news:Ste1978.24lqsz_1142243102.7492@excelforum-nospam.com...
>
> Hi thanks for the reply,
>
> I still don't know how to start, I have hardly any excel training at
> all!
>
> I just want a step by step guide on how to begin? I read the help for
> VLOOKUP but I still do not understand.
>
>
> --
> Ste1978
> ------------------------------------------------------------------------
> Ste1978's Profile:
> http://www.excelforum.com/member.php...o&userid=32398
> View this thread: http://www.excelforum.com/showthread...hreadid=521647
>

Ste1978
Here's a little help on that:

SHEET1
A B

1 1.4 3RV1011-1BA10.
2 1.2 3RV1011-1BA10.
3 1.3 3RV1011-1BA12.
5 1.6 3RV1011-1BA13.
6 2 3RV1011-1BA14.
Let's assume this is your setup
SHEET2 ( optional, can be on same sheet too)
A B
1 1.4 =VLOOKUP(A1,Sheet1!A1:C5,2,FALSE)

will return:

A B
1 1.4 3RV1011-1BA10.

http://www.contextures.com/excelfiles.html
has a good explanation

=VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted)
The ItemToFind is a single item specified by the user.
The RangeToLookIn is the range of data with the row headings at the left
hand side.
The ColumnToPickFrom is how far across the table the function should look to
pick from.
The Sorted/Unsorted is whether the column headings are sorted. TRUE for yes,
FALSE for no.

HTH





"Norman Jones" wrote:

> Hi Ste1978,
>
> > I just want a step by step guide on how to begin? I read the help for
> > VLOOKUP but I still do not understand.
>
> See Debra Dalgleish's VLookup tutorial at:
>
> http://www.contextures.com/xlFunctions02.html
>
>
> ---
> Regards,
> Norman
>
>
>
> "Ste1978" <Ste1978.24lqsz_1142243102.7492@excelforum-nospam.com> wrote in
> message news:Ste1978.24lqsz_1142243102.7492@excelforum-nospam.com...
> >
> > Hi thanks for the reply,
> >
> > I still don't know how to start, I have hardly any excel training at
> > all!
> >
> > I just want a step by step guide on how to begin? I read the help for
> > VLOOKUP but I still do not understand.
> >
> >
> > --
> > Ste1978
> > ------------------------------------------------------------------------
> > Ste1978's Profile:
> > http://www.excelforum.com/member.php...o&userid=32398
> > View this thread: http://www.excelforum.com/showthread...hreadid=521647
> >
>
>
>

Thanks very much. I have nearly completed my spreadsheet due to your help. The only point im now stuck on is as follows:-

This is my setup at the moment

Sheet1(Codes)
A B
1 FLC(A) PART NUMBER
2 0.11 3RV1011-0AA10
3 0.12 3RV1011-0AA10
4 0.13 3RV1011-0AA10
5 0.14 3RV1011-0BA10
6 0.15 3RV1011-0BA10
7 0.16 3RV1011-0BA10

Sheet2(3RV Breakers)
A B
1 =VLOOKUP(B5,Codes!$A$2:$B$91,2,FALSE)

so if I enter '0.12' in A1 then I get a return of '3RV1011-0AA10' B1 which is what I want. However, on sheet1 can I enter a range i.e. 0.11 to 013 which will still give me 3RV1011-0AA10? instead of listing the ranges individually, for example I have a range of 0.7 to 1.0 which all equal 3RV1011-0JA10 but I dont want to list on sheet1 an entry of 0.7, 0.71, 0.72 etc..

Thanks again for the already sterling advice.

Originally Posted by Ste1978

Thanks very much. I have nearly completed my spreadsheet due to your help. The only point im now stuck on is as follows:-

This is my setup at the moment

Sheet1(Codes)
A B
1 FLC(A) PART NUMBER
2 0.11 3RV1011-0AA10
3 0.12 3RV1011-0AA10
4 0.13 3RV1011-0AA10
5 0.14 3RV1011-0BA10
6 0.15 3RV1011-0BA10
7 0.16 3RV1011-0BA10

Sheet2(3RV Breakers)
A B
1 =VLOOKUP(B5,Codes!$A$2:$B$91,2,FALSE)

so if I enter '0.12' in A1 then I get a return of '3RV1011-0AA10' B1 which is what I want. However, on sheet1 can I enter a range i.e. 0.11 to 013 which will still give me 3RV1011-0AA10? instead of listing the ranges individually, for example I have a range of 0.7 to 1.0 which all equal 3RV1011-0JA10 but I dont want to list on sheet1 an entry of 0.7, 0.71, 0.72 etc..

Thanks again for the already sterling advice.

Use TRUE instead of FALSE.
Suppose your data is

0.7 3RVXXXXXX
1.0 3RVYYYYYY
1.4 3RVZZZZZZ etc
2.0 3RVQQQQQ
then using TRUE will return 3RVXXXXXX for any value between .7 and less than 1.0, return 3RVYYYYYYY for value between 1.0 and less than 1.4 and return 3RVZZZZZZZ for all values between 1.4 and less than 2.0

A V Veerkar