Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Problem
I know that there will be a simple solution to this but have been playing
with it for 1/2 hour now and not making progress! I have a table of data (say A1:Z100). An extra column is added each week when various figures are received. Hence, at the moment, I have a list of branches in column A and dates across the top in Row 1. All I need to do is to populate a summary with data extracted from the most recent figures. I can do this easily with VLOOKUP but how do I programme it such that Col_index_num returns data from the column with the most recent date in Row 1? I have tried using MAX but can't seem to find the right syntax. Many thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Problem
As ALWAYS, you should post your code for comments. If you want to find the last column in row 1 lastcol=cells(1,columns.count).end(xltoleft).colum n -- Don Guillett Microsoft MVP Excel SalesAid Software "Terry Bennett" wrote in message ... I know that there will be a simple solution to this but have been playing with it for 1/2 hour now and not making progress! I have a table of data (say A1:Z100). An extra column is added each week when various figures are received. Hence, at the moment, I have a list of branches in column A and dates across the top in Row 1. All I need to do is to populate a summary with data extracted from the most recent figures. I can do this easily with VLOOKUP but how do I programme it such that Col_index_num returns data from the column with the most recent date in Row 1? I have tried using MAX but can't seem to find the right syntax. Many thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Problem
Hi,
To get the last value in a row use this which is for row 2. The formula can be dragged down for subsequent rows. =INDEX(2:2,MATCH(6.022*10^23,2:2)) Mike "Terry Bennett" wrote: I know that there will be a simple solution to this but have been playing with it for 1/2 hour now and not making progress! I have a table of data (say A1:Z100). An extra column is added each week when various figures are received. Hence, at the moment, I have a list of branches in column A and dates across the top in Row 1. All I need to do is to populate a summary with data extracted from the most recent figures. I can do this easily with VLOOKUP but how do I programme it such that Col_index_num returns data from the column with the most recent date in Row 1? I have tried using MAX but can't seem to find the right syntax. Many thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Problem
Thanks Mike. That seems to work fine but I'm intrigued with the syntax.
Can you enlighten me with the "6.022*10^23" part?! "Mike H" wrote in message ... Hi, To get the last value in a row use this which is for row 2. The formula can be dragged down for subsequent rows. =INDEX(2:2,MATCH(6.022*10^23,2:2)) Mike "Terry Bennett" wrote: I know that there will be a simple solution to this but have been playing with it for 1/2 hour now and not making progress! I have a table of data (say A1:Z100). An extra column is added each week when various figures are received. Hence, at the moment, I have a list of branches in column A and dates across the top in Row 1. All I need to do is to populate a summary with data extracted from the most recent figures. I can do this easily with VLOOKUP but how do I programme it such that Col_index_num returns data from the column with the most recent date in Row 1? I have tried using MAX but can't seem to find the right syntax. Many thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Problem
Mine was, IF you were using a macro. You didn't say.
-- Don Guillett Microsoft MVP Excel SalesAid Software "Terry Bennett" wrote in message ... Don. I appreciate you taking the trouble to reply but you're assuming a greater level of knowledge than I possess! I was looking for a function/functions and don't understand your suggestion. "Don Guillett" wrote in message ... As ALWAYS, you should post your code for comments. If you want to find the last column in row 1 lastcol=cells(1,columns.count).end(xltoleft).colum n -- Don Guillett Microsoft MVP Excel SalesAid Software "Terry Bennett" wrote in message ... I know that there will be a simple solution to this but have been playing with it for 1/2 hour now and not making progress! I have a table of data (say A1:Z100). An extra column is added each week when various figures are received. Hence, at the moment, I have a list of branches in column A and dates across the top in Row 1. All I need to do is to populate a summary with data extracted from the most recent figures. I can do this easily with VLOOKUP but how do I programme it such that Col_index_num returns data from the column with the most recent date in Row 1? I have tried using MAX but can't seem to find the right syntax. Many thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Problem
I was grateful to Mike for his response to my query back in October and the
suggestion works fine. I have, however, been puzzled by the syntax ever since. Could somebody explain to me the "6.022*10^23" element? I'm presuming that the '*' and '^' are wildcards of some sort but haven't been able to work it out! Many thanks. "Mike H" wrote in message ... Hi, To get the last value in a row use this which is for row 2. The formula can be dragged down for subsequent rows. =INDEX(2:2,MATCH(6.022*10^23,2:2)) Mike "Terry Bennett" wrote: I know that there will be a simple solution to this but have been playing with it for 1/2 hour now and not making progress! I have a table of data (say A1:Z100). An extra column is added each week when various figures are received. Hence, at the moment, I have a list of branches in column A and dates across the top in Row 1. All I need to do is to populate a summary with data extracted from the most recent figures. I can do this easily with VLOOKUP but how do I programme it such that Col_index_num returns data from the column with the most recent date in Row 1? I have tried using MAX but can't seem to find the right syntax. Many thanks. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Problem
If you are unfamiliar with Excel's arithmetic operators, I would suggest
that you type the term "arithmetic operators" into Excel help. The * symbol in this context is a multiplication, not a wildcard. The formula =2*3 gives the answer 6, which is the result of mutliplying 2 by 3. The symbol ^ is the exponentiation or power operator. 10^23 means 10 to the power of 23, or 1 with 23 zeroes after it (or 1E+23). =10^23 is the same as =POWER(10,23) In the suggested formula, Mike was doubtless trying to suggest a very large number, and his choice wasn't entirely arbitrary. Google will explain its significance. -- David Biddulph "Terry Bennett" wrote in message ... I was grateful to Mike for his response to my query back in October and the suggestion works fine. I have, however, been puzzled by the syntax ever since. Could somebody explain to me the "6.022*10^23" element? I'm presuming that the '*' and '^' are wildcards of some sort but haven't been able to work it out! Many thanks. "Mike H" wrote in message ... Hi, To get the last value in a row use this which is for row 2. The formula can be dragged down for subsequent rows. =INDEX(2:2,MATCH(6.022*10^23,2:2)) Mike "Terry Bennett" wrote: I know that there will be a simple solution to this but have been playing with it for 1/2 hour now and not making progress! I have a table of data (say A1:Z100). An extra column is added each week when various figures are received. Hence, at the moment, I have a list of branches in column A and dates across the top in Row 1. All I need to do is to populate a summary with data extracted from the most recent figures. I can do this easily with VLOOKUP but how do I programme it such that Col_index_num returns data from the column with the most recent date in Row 1? I have tried using MAX but can't seem to find the right syntax. Many thanks. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Problem
That's just a giant number.
^ is the power operation For instance: 2^4 is the same as 2*2*2*2 (16) And the asterisk is just the multiply operation. Type: =6.022*10^23 and you'll see how big a number it represents. Terry Bennett wrote: I was grateful to Mike for his response to my query back in October and the suggestion works fine. I have, however, been puzzled by the syntax ever since. Could somebody explain to me the "6.022*10^23" element? I'm presuming that the '*' and '^' are wildcards of some sort but haven't been able to work it out! Many thanks. "Mike H" wrote in message ... Hi, To get the last value in a row use this which is for row 2. The formula can be dragged down for subsequent rows. =INDEX(2:2,MATCH(6.022*10^23,2:2)) Mike "Terry Bennett" wrote: I know that there will be a simple solution to this but have been playing with it for 1/2 hour now and not making progress! I have a table of data (say A1:Z100). An extra column is added each week when various figures are received. Hence, at the moment, I have a list of branches in column A and dates across the top in Row 1. All I need to do is to populate a summary with data extracted from the most recent figures. I can do this easily with VLOOKUP but how do I programme it such that Col_index_num returns data from the column with the most recent date in Row 1? I have tried using MAX but can't seem to find the right syntax. Many thanks. -- Dave Peterson |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Problem
Ah, yes, the Avogadro constant.
Thanks David. "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... If you are unfamiliar with Excel's arithmetic operators, I would suggest that you type the term "arithmetic operators" into Excel help. The * symbol in this context is a multiplication, not a wildcard. The formula =2*3 gives the answer 6, which is the result of mutliplying 2 by 3. The symbol ^ is the exponentiation or power operator. 10^23 means 10 to the power of 23, or 1 with 23 zeroes after it (or 1E+23). =10^23 is the same as =POWER(10,23) In the suggested formula, Mike was doubtless trying to suggest a very large number, and his choice wasn't entirely arbitrary. Google will explain its significance. -- David Biddulph "Terry Bennett" wrote in message ... I was grateful to Mike for his response to my query back in October and the suggestion works fine. I have, however, been puzzled by the syntax ever since. Could somebody explain to me the "6.022*10^23" element? I'm presuming that the '*' and '^' are wildcards of some sort but haven't been able to work it out! Many thanks. "Mike H" wrote in message ... Hi, To get the last value in a row use this which is for row 2. The formula can be dragged down for subsequent rows. =INDEX(2:2,MATCH(6.022*10^23,2:2)) Mike "Terry Bennett" wrote: I know that there will be a simple solution to this but have been playing with it for 1/2 hour now and not making progress! I have a table of data (say A1:Z100). An extra column is added each week when various figures are received. Hence, at the moment, I have a list of branches in column A and dates across the top in Row 1. All I need to do is to populate a summary with data extracted from the most recent figures. I can do this easily with VLOOKUP but how do I programme it such that Col_index_num returns data from the column with the most recent date in Row 1? I have tried using MAX but can't seem to find the right syntax. Many thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Problem, Please Help! | Excel Discussion (Misc queries) | |||
Vlookup problem | New Users to Excel | |||
VLOOKUP problem (N/V) | Excel Worksheet Functions | |||
VLOOKUP problem | Excel Worksheet Functions | |||
New VLOOKUP problem | Excel Worksheet Functions |