I recieved some good input over the weekend (which I so appreciate), but not
the info I need.
I have to create a spreadsheet for an audit (not $$). This is not a math
issue.
I think it is some sort of "IF" function. I have around 80 of these
folumulars I need to create that will each be used several hundred times.
For instance: Column A C E
line 1 123 456 789
If I type 123 in any subsequent line I need 456 in column C and 789 in
column E
Seems like it would be simple, but I sure don't know how.
I am not the sharpest knife in the drawer(probably the dullest) when it
comes to functions and fomulars so speak slowly and in simple terms.
--
Novice

Somewhere else in your spreadsheet (eg in columns X, Y and Z), build
up a table of the 80 or so values that you expect. This would look
something like this:

X Y Z
123 456 789
124 654 987

and so on down to row 80 or so.

Then in C2 you can enter this formula:

=IF(A2="","",VLOOKUP(A2,X$1:Z$100,2,0))

and this one in E2:

=IF(A2="","",VLOOKUP(A2,X$1:Z$100,3,0))

and then just copy these two down as far as you think you need.

When you enter a number in column A, as long as it is within column X
of your pre-defined table, then the other numbers will appear in
columns C and E. If the number in A does not appear in column X then
you will get the error #N/A.

Hope this helps.

Pete



On Jan 12, 5:04*pm, drnlds wrote:
I recieved some good input over the weekend (which I so appreciate), but not
the info I need.
I have to create a spreadsheet for an audit (not $$). *This is not a math
issue.
I think it is some sort of "IF" function. *I have around 80 of these
folumulars I need to create that will each be used several hundred times.
For instance: * *Column * *A * * * * * * C * * * * * * * E
* * *line 1 * * * * * * * * * * *123 * * * * *456 * * * * * * 789
If I type 123 in any subsequent line I need 456 in column C and 789 in
column E
Seems like it would be simple, but I sure don't know how.
I am not the sharpest knife in the drawer(probably the dullest) when it
comes to functions and fomulars so speak slowly and in simple terms.
--
Novice

This will for sure get me started. If I need more, I'll be back.
Thank you so much.
--
Novice


"Pete_UK" wrote:

Somewhere else in your spreadsheet (eg in columns X, Y and Z), build
up a table of the 80 or so values that you expect. This would look
something like this:

X Y Z
123 456 789
124 654 987

and so on down to row 80 or so.

Then in C2 you can enter this formula:

=IF(A2="","",VLOOKUP(A2,X$1:Z$100,2,0))

and this one in E2:

=IF(A2="","",VLOOKUP(A2,X$1:Z$100,3,0))

and then just copy these two down as far as you think you need.

When you enter a number in column A, as long as it is within column X
of your pre-defined table, then the other numbers will appear in
columns C and E. If the number in A does not appear in column X then
you will get the error #N/A.

Hope this helps.

Pete



On Jan 12, 5:04 pm, drnlds wrote:
I recieved some good input over the weekend (which I so appreciate), but not
the info I need.
I have to create a spreadsheet for an audit (not $$). This is not a math
issue.
I think it is some sort of "IF" function. I have around 80 of these
folumulars I need to create that will each be used several hundred times.
For instance: Column A C E
line 1 123 456 789
If I type 123 in any subsequent line I need 456 in column C and 789 in
column E
Seems like it would be simple, but I sure don't know how.
I am not the sharpest knife in the drawer(probably the dullest) when it
comes to functions and fomulars so speak slowly and in simple terms.
--
Novice

You're welcome - thanks for feeding back.

Pete

On Jan 12, 5:51*pm, drnlds wrote:
This will for sure get me started. *If I need more, I'll be back.
Thank you so much.
--
Novice



"Pete_UK" wrote:
Somewhere else in your spreadsheet (eg in columns X, Y and Z), build
up a table of the 80 or so values that you expect. This would look
something like this:

* X * * * *Y * * * *Z
123 * * 456 * * 789
124 * * 654 * * 987

and so on down to row 80 or so.

Then in C2 you can enter this formula:

=IF(A2="","",VLOOKUP(A2,X$1:Z$100,2,0))

and this one in E2:

=IF(A2="","",VLOOKUP(A2,X$1:Z$100,3,0))

and then just copy these two down as far as you think you need.

When you enter a number in column A, as long as it is within column X
of your pre-defined table, then the other numbers will appear in
columns C and E. If the number in A does not appear in column X then
you will get the error #N/A.

Hope this helps.

Pete

On Jan 12, 5:04 pm, drnlds wrote:
I recieved some good input over the weekend (which I so appreciate), but not
the info I need.
I have to create a spreadsheet for an audit (not $$). *This is not a math
issue.
I think it is some sort of "IF" function. *I have around 80 of these
folumulars I need to create that will each be used several hundred times.
For instance: * *Column * *A * * * * * * C * * * * * * * E
* * *line 1 * * * * * * * * * * *123 * * * * *456 * * * * * * 789
If I type 123 in any subsequent line I need 456 in column C and 789 in
column E
Seems like it would be simple, but I sure don't know how.
I am not the sharpest knife in the drawer(probably the dullest) when it
comes to functions and fomulars so speak slowly and in simple terms.
--
Novice- Hide quoted text -

- Show quoted text -

THANK YOU THANK YOU THANK YOU
I have worked on this for several days with no success. Your input was
exactly what I needed. What is function called?
Diane

--
Novice


"Pete_UK" wrote:

Somewhere else in your spreadsheet (eg in columns X, Y and Z), build
up a table of the 80 or so values that you expect. This would look
something like this:

X Y Z
123 456 789
124 654 987

and so on down to row 80 or so.

Then in C2 you can enter this formula:

=IF(A2="","",VLOOKUP(A2,X$1:Z$100,2,0))

and this one in E2:

=IF(A2="","",VLOOKUP(A2,X$1:Z$100,3,0))

and then just copy these two down as far as you think you need.

When you enter a number in column A, as long as it is within column X
of your pre-defined table, then the other numbers will appear in
columns C and E. If the number in A does not appear in column X then
you will get the error #N/A.

Hope this helps.

Pete



On Jan 12, 5:04 pm, drnlds wrote:
I recieved some good input over the weekend (which I so appreciate), but not
the info I need.
I have to create a spreadsheet for an audit (not $$). This is not a math
issue.
I think it is some sort of "IF" function. I have around 80 of these
folumulars I need to create that will each be used several hundred times.
For instance: Column A C E
line 1 123 456 789
If I type 123 in any subsequent line I need 456 in column C and 789 in
column E
Seems like it would be simple, but I sure don't know how.
I am not the sharpest knife in the drawer(probably the dullest) when it
comes to functions and fomulars so speak slowly and in simple terms.
--
Novice

Yes, I saw some of your earlier posts, Diane. Glad it worked for you.

Actually, there are two functions involved - IF and VLOOKUP. You can
get more details about them if you look in Excel Help.

Pete

On Jan 12, 7:34*pm, drnlds wrote:
THANK YOU * * * THANK YOU * * *THANK YOU
I have worked on this for several days with no success. *Your input was
exactly what I needed. *What is function called?
Diane

--
Novice



"Pete_UK" wrote:
Somewhere else in your spreadsheet (eg in columns X, Y and Z), build
up a table of the 80 or so values that you expect. This would look
something like this:

* X * * * *Y * * * *Z
123 * * 456 * * 789
124 * * 654 * * 987

and so on down to row 80 or so.

Then in C2 you can enter this formula:

=IF(A2="","",VLOOKUP(A2,X$1:Z$100,2,0))

and this one in E2:

=IF(A2="","",VLOOKUP(A2,X$1:Z$100,3,0))

and then just copy these two down as far as you think you need.

When you enter a number in column A, as long as it is within column X
of your pre-defined table, then the other numbers will appear in
columns C and E. If the number in A does not appear in column X then
you will get the error #N/A.

Hope this helps.

Pete

On Jan 12, 5:04 pm, drnlds wrote:
I recieved some good input over the weekend (which I so appreciate), but not
the info I need.
I have to create a spreadsheet for an audit (not $$). *This is not a math
issue.
I think it is some sort of "IF" function. *I have around 80 of these
folumulars I need to create that will each be used several hundred times.
For instance: * *Column * *A * * * * * * C * * * * * * * E
* * *line 1 * * * * * * * * * * *123 * * * * *456 * * * * * * 789
If I type 123 in any subsequent line I need 456 in column C and 789 in
column E
Seems like it would be simple, but I sure don't know how.
I am not the sharpest knife in the drawer(probably the dullest) when it
comes to functions and fomulars so speak slowly and in simple terms.
--
Novice- Hide quoted text -

- Show quoted text -