Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get the last number from a specific column
I have my producers listed across the top of my spreedsheet, they are listed
by their producer number, and their pounds are listed below, sample below 220000 223300 456700 345400 675400 3300 4500 4501 2345 3456 4567 2345 6754 5678 2343 Below this I would like to enter a producer number and I want the computer to give me the last pounds listed for that producer. Can anyone help me? 22300 result |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get the last number from a specific column
On Sun, 14 May 2006 19:31:01 -0700, milktruck
wrote: I have my producers listed across the top of my spreedsheet, they are listed by their producer number, and their pounds are listed below, sample below 220000 223300 456700 345400 675400 3300 4500 4501 2345 3456 4567 2345 6754 5678 2343 Below this I would like to enter a producer number and I want the computer to give me the last pounds listed for that producer. Can anyone help me? 22300 result If your producer numbers are in row 1 and the table starts in column A, then, if your entry cell for the producer number to query is I2: =INDEX(OFFSET(A:A,0,MATCH(I2,1:1,0)-1),MATCH( 9.9999E+307,OFFSET(A:A,0,MATCH(I2,1:1,0)-1))) will give the last entry in I2's column. --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get the last number from a specific column
Hi!
Try this: Assume your table is in the range A1:En G1 = producer number =LOOKUP(9.99999999999999E+307,OFFSET(A1,,MATCH(G1, A1:E1,0)-1,65536)) Note: 65536 refers to the size of the entire column where a match is found of the producer number. This can be reduced to more reasonable number. If there are no empty cells within the table you could use something like: =LOOKUP(9.99999999999999E+307,OFFSET(A1,,MATCH(G1, A1:E1,0)-1,COUNTA(A:A))) Biff "milktruck" wrote in message ... I have my producers listed across the top of my spreedsheet, they are listed by their producer number, and their pounds are listed below, sample below 220000 223300 456700 345400 675400 3300 4500 4501 2345 3456 4567 2345 6754 5678 2343 Below this I would like to enter a producer number and I want the computer to give me the last pounds listed for that producer. Can anyone help me? 22300 result |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get the last number from a specific column
Using the formula you provided, will I be able to change the producer number
and get the results for that producer without changing the formula? If I get this to work, I'm going to have a list of producer numbers (route for the day), the pounds will be provided and I'll then do a sum of the pounds and be able to tell if those producers will all fit on my load. "milktruck" wrote: I have my producers listed across the top of my spreedsheet, they are listed by their producer number, and their pounds are listed below, sample below 220000 223300 456700 345400 675400 3300 4500 4501 2345 3456 4567 2345 6754 5678 2343 Below this I would like to enter a producer number and I want the computer to give me the last pounds listed for that producer. Can anyone help me? 22300 result |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get the last number from a specific column
Using the formula you provided, will I be able to change the producer
number and get the results for that producer without changing the formula? Yes, using either formula provided. You enter the producer number in a cell and refer to that cell rather than changing the formula every time you want to change the producer number. Ron's formula refers to cell I2 as holding the producer number. My formula refers to cell G1 as holding the producer number. These are just examples and you can use whatever cell you want. Biff "milktruck" wrote in message ... Using the formula you provided, will I be able to change the producer number and get the results for that producer without changing the formula? If I get this to work, I'm going to have a list of producer numbers (route for the day), the pounds will be provided and I'll then do a sum of the pounds and be able to tell if those producers will all fit on my load. "milktruck" wrote: I have my producers listed across the top of my spreedsheet, they are listed by their producer number, and their pounds are listed below, sample below 220000 223300 456700 345400 675400 3300 4500 4501 2345 3456 4567 2345 6754 5678 2343 Below this I would like to enter a producer number and I want the computer to give me the last pounds listed for that producer. Can anyone help me? 22300 result |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get the last number from a specific column
The formula you provided works well. Now, I would like to know if I put my
route for the day on worksheet 1 and my producer list with their pounds on worksheet 2, how would I get the formula to work or is that not possible? Any suggestions. Thanks. "Biff" wrote: Using the formula you provided, will I be able to change the producer number and get the results for that producer without changing the formula? Yes, using either formula provided. You enter the producer number in a cell and refer to that cell rather than changing the formula every time you want to change the producer number. Ron's formula refers to cell I2 as holding the producer number. My formula refers to cell G1 as holding the producer number. These are just examples and you can use whatever cell you want. Biff "milktruck" wrote in message ... Using the formula you provided, will I be able to change the producer number and get the results for that producer without changing the formula? If I get this to work, I'm going to have a list of producer numbers (route for the day), the pounds will be provided and I'll then do a sum of the pounds and be able to tell if those producers will all fit on my load. "milktruck" wrote: I have my producers listed across the top of my spreedsheet, they are listed by their producer number, and their pounds are listed below, sample below 220000 223300 456700 345400 675400 3300 4500 4501 2345 3456 4567 2345 6754 5678 2343 Below this I would like to enter a producer number and I want the computer to give me the last pounds listed for that producer. Can anyone help me? 22300 result |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get the last number from a specific column
Why not ditch volatile OFFSET?...
=LOOKUP(9.99999999999999E+307,INDEX(A:E,0,MATCH(G1 ,A1:E1,0))) Biff wrote: Hi! Try this: Assume your table is in the range A1:En G1 = producer number =LOOKUP(9.99999999999999E+307,OFFSET(A1,,MATCH(G1, A1:E1,0)-1,65536)) Note: 65536 refers to the size of the entire column where a match is found of the producer number. This can be reduced to more reasonable number. If there are no empty cells within the table you could use something like: =LOOKUP(9.99999999999999E+307,OFFSET(A1,,MATCH(G1, A1:E1,0)-1,COUNTA(A:A))) Biff "milktruck" wrote in message ... I have my producers listed across the top of my spreedsheet, they are listed by their producer number, and their pounds are listed below, sample below 220000 223300 456700 345400 675400 3300 4500 4501 2345 3456 4567 2345 6754 5678 2343 Below this I would like to enter a producer number and I want the computer to give me the last pounds listed for that producer. Can anyone help me? 22300 result |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display missing Part Number if Column A does not match column B | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
how do I find an average number of specific words in a column | New Users to Excel |