| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
What am I doing wrong
vLookup Problem never ceases
Dear someone Im having a problem with a vlookup formula, with (last cell/row always varying) what do I do to copy the last cell with data and paste it to another sheet. This formula below finds the first sum not the last. Or Is there another way to copy last cell with data and paste to another sheet? =IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE)) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What am I doing wrong
If you review the function..
=VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) The col_index_num is the column number within the table_array. Here there are only 28 columns in the table array $A$5:$AF$32. If you are looking to retrieve the last column of the above array it should be 28 instead of 32. =VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: vLookup Problem never ceases Dear someone Im having a problem with a vlookup formula, with (last cell/row always varying) what do I do to copy the last cell with data and paste it to another sheet. This formula below finds the first sum not the last. Or Is there another way to copy last cell with data and paste to another sheet? =IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE)) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What am I doing wrong
Im sure I can count the number of columns and it is 32, however I copied your
formula and it returns 0 so I change 28 to 32 and it returned the first sum instead of the last which is the total. 6 6 4 5 21 I have tried this code but the problem is it adds up all the sums including the total so I get double the total e.g 42 should be 21 =SUM(Employee1!AF:AF) "Jacob Skaria" wrote: If you review the function.. =VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) The col_index_num is the column number within the table_array. Here there are only 28 columns in the table array $A$5:$AF$32. If you are looking to retrieve the last column of the above array it should be 28 instead of 32. =VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: vLookup Problem never ceases Dear someone Im having a problem with a vlookup formula, with (last cell/row always varying) what do I do to copy the last cell with data and paste it to another sheet. This formula below finds the first sum not the last. Or Is there another way to copy last cell with data and paste to another sheet? =IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE)) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What am I doing wrong
Hi
As the last cell in column AF is a total, I suppose you have a row heading in the total line column A equal to "Total", try this sumif formula: =SUMIF(Employee1!A5:A32;"<Total";Employee1!AF5:AF 32) Hopes this helps. .... Per "aussiegirlone" skrev i meddelelsen ... Im sure I can count the number of columns and it is 32, however I copied your formula and it returns 0 so I change 28 to 32 and it returned the first sum instead of the last which is the total. 6 6 4 5 21 I have tried this code but the problem is it adds up all the sums including the total so I get double the total e.g 42 should be 21 =SUM(Employee1!AF:AF) "Jacob Skaria" wrote: If you review the function.. =VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) The col_index_num is the column number within the table_array. Here there are only 28 columns in the table array $A$5:$AF$32. If you are looking to retrieve the last column of the above array it should be 28 instead of 32. =VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: vLookup Problem never ceases Dear someone Im having a problem with a vlookup formula, with (last cell/row always varying) what do I do to copy the last cell with data and paste it to another sheet. This formula below finds the first sum not the last. Or Is there another way to copy last cell with data and paste to another sheet? =IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE)) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What am I doing wrong
Oops...Sorry ...i would have been in another world while replying earlier..
Below mentioned is one way to retrieve the last value from AF for the matching valuue in Col A. 'array formula =INDIRECT(ADDRESS(MAX(IF(Employee1!$A$5:$A$32=A5,R OW(Employee1!$A$5:$A$32))),32)) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: Im sure I can count the number of columns and it is 32, however I copied your formula and it returns 0 so I change 28 to 32 and it returned the first sum instead of the last which is the total. 6 6 4 5 21 I have tried this code but the problem is it adds up all the sums including the total so I get double the total e.g 42 should be 21 =SUM(Employee1!AF:AF) "Jacob Skaria" wrote: If you review the function.. =VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) The col_index_num is the column number within the table_array. Here there are only 28 columns in the table array $A$5:$AF$32. If you are looking to retrieve the last column of the above array it should be 28 instead of 32. =VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: vLookup Problem never ceases Dear someone Im having a problem with a vlookup formula, with (last cell/row always varying) what do I do to copy the last cell with data and paste it to another sheet. This formula below finds the first sum not the last. Or Is there another way to copy last cell with data and paste to another sheet? =IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE)) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What am I doing wrong
I dont understand why the codes you both have provided still returns 0
"Jacob Skaria" wrote: Oops...Sorry ...i would have been in another world while replying earlier.. Below mentioned is one way to retrieve the last value from AF for the matching valuue in Col A. 'array formula =INDIRECT(ADDRESS(MAX(IF(Employee1!$A$5:$A$32=A5,R OW(Employee1!$A$5:$A$32))),32)) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: Im sure I can count the number of columns and it is 32, however I copied your formula and it returns 0 so I change 28 to 32 and it returned the first sum instead of the last which is the total. 6 6 4 5 21 I have tried this code but the problem is it adds up all the sums including the total so I get double the total e.g 42 should be 21 =SUM(Employee1!AF:AF) "Jacob Skaria" wrote: If you review the function.. =VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) The col_index_num is the column number within the table_array. Here there are only 28 columns in the table array $A$5:$AF$32. If you are looking to retrieve the last column of the above array it should be 28 instead of 32. =VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: vLookup Problem never ceases Dear someone Im having a problem with a vlookup formula, with (last cell/row always varying) what do I do to copy the last cell with data and paste it to another sheet. This formula below finds the first sum not the last. Or Is there another way to copy last cell with data and paste to another sheet? =IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE)) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What am I doing wrong
Aussiegirlone, can you supply a sample workbook? aussiegirlone;421660 Wrote: I dont understand why the codes you both have provided still returns 0 "Jacob Skaria" wrote: Oops...Sorry ...i would have been in another world while replying earlier.. Below mentioned is one way to retrieve the last value from AF for the matching valuue in Col A. 'array formula =INDIRECT(ADDRESS(MAX(IF(Employee1!$A$5:$A$32=A5,R OW(Employee1!$A$5:$A$32))),32)) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: Im sure I can count the number of columns and it is 32, however I copied your formula and it returns 0 so I change 28 to 32 and it returned the first sum instead of the last which is the total. 6 6 4 5 21 I have tried this code but the problem is it adds up all the sums including the total so I get double the total e.g 42 should be 21 =SUM(Employee1!AF:AF) "Jacob Skaria" wrote: If you review the function.. =VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) The col_index_num is the column number within the table_array. Here there are only 28 columns in the table array $A$5:$AF$32. If you are looking to retrieve the last column of the above array it should be 28 instead of 32. =VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: vLookup Problem never ceases Dear someone Im having a problem with a vlookup formula, with (last cell/row always varying) what do I do to copy the last cell with data and paste it to another sheet. This formula below finds the first sum not the last. Or Is there another way to copy last cell with data and paste to another sheet? =IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE)) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117308 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What am I doing wrong
OK. Let us try the below.
With a table as below try the below formula which retrives the last matching value for 'test'. You can adjust this to suit for your requirement.. ColA ColB test 1 apple 32 test 2 apple 45 apple 52 boy 23 test 3 =LOOKUP(2,1/(A1:A10="test"),B1:B10) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: I dont understand why the codes you both have provided still returns 0 "Jacob Skaria" wrote: Oops...Sorry ...i would have been in another world while replying earlier.. Below mentioned is one way to retrieve the last value from AF for the matching valuue in Col A. 'array formula =INDIRECT(ADDRESS(MAX(IF(Employee1!$A$5:$A$32=A5,R OW(Employee1!$A$5:$A$32))),32)) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: Im sure I can count the number of columns and it is 32, however I copied your formula and it returns 0 so I change 28 to 32 and it returned the first sum instead of the last which is the total. 6 6 4 5 21 I have tried this code but the problem is it adds up all the sums including the total so I get double the total e.g 42 should be 21 =SUM(Employee1!AF:AF) "Jacob Skaria" wrote: If you review the function.. =VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) The col_index_num is the column number within the table_array. Here there are only 28 columns in the table array $A$5:$AF$32. If you are looking to retrieve the last column of the above array it should be 28 instead of 32. =VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: vLookup Problem never ceases Dear someone Im having a problem with a vlookup formula, with (last cell/row always varying) what do I do to copy the last cell with data and paste it to another sheet. This formula below finds the first sum not the last. Or Is there another way to copy last cell with data and paste to another sheet? =IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE)) |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What am I doing wrong
I opened a new Excel workbook testing the code but I get N/A
As another solution to my problem is to do something Stupid like this.... in cell D5 on sheet where copy of total will be =SUM(Employee1!AF:AF), then in Cell C5 =Sum D5/2. although its double the trouble I am getting a true result, otherwise, I don't know what else to do. It seems strange to me as the vLookup use to work but I noticed that every few days when I cleared the data on employee sheet this caused the vlookup to become a #REF# Anyhow, I think I'll just let it go! Thankyou all very much for all your help I do appreciate it. "Jacob Skaria" wrote: OK. Let us try the below. With a table as below try the below formula which retrives the last matching value for 'test'. You can adjust this to suit for your requirement.. ColA ColB test 1 apple 32 test 2 apple 45 apple 52 boy 23 test 3 =LOOKUP(2,1/(A1:A10="test"),B1:B10) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: I dont understand why the codes you both have provided still returns 0 "Jacob Skaria" wrote: Oops...Sorry ...i would have been in another world while replying earlier.. Below mentioned is one way to retrieve the last value from AF for the matching valuue in Col A. 'array formula =INDIRECT(ADDRESS(MAX(IF(Employee1!$A$5:$A$32=A5,R OW(Employee1!$A$5:$A$32))),32)) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: Im sure I can count the number of columns and it is 32, however I copied your formula and it returns 0 so I change 28 to 32 and it returned the first sum instead of the last which is the total. 6 6 4 5 21 I have tried this code but the problem is it adds up all the sums including the total so I get double the total e.g 42 should be 21 =SUM(Employee1!AF:AF) "Jacob Skaria" wrote: If you review the function.. =VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) The col_index_num is the column number within the table_array. Here there are only 28 columns in the table array $A$5:$AF$32. If you are looking to retrieve the last column of the above array it should be 28 instead of 32. =VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: vLookup Problem never ceases Dear someone Im having a problem with a vlookup formula, with (last cell/row always varying) what do I do to copy the last cell with data and paste it to another sheet. This formula below finds the first sum not the last. Or Is there another way to copy last cell with data and paste to another sheet? =IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE)) |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What am I doing wrong
Note that for the below example the formula used is not VLOOKUP() but ***
LOOKUP() *** =LOOKUP(2,1/(A1:A10="test"),B1:B10) If this post helps click Yes --------------- Jacob Skaria "Simon Lloyd" wrote: Aussiegirlone, can you supply a sample workbook? aussiegirlone;421660 Wrote: I dont understand why the codes you both have provided still returns 0 "Jacob Skaria" wrote: Oops...Sorry ...i would have been in another world while replying earlier.. Below mentioned is one way to retrieve the last value from AF for the matching valuue in Col A. 'array formula =INDIRECT(ADDRESS(MAX(IF(Employee1!$A$5:$A$32=A5,R OW(Employee1!$A$5:$A$32))),32)) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: Im sure I can count the number of columns and it is 32, however I copied your formula and it returns 0 so I change 28 to 32 and it returned the first sum instead of the last which is the total. 6 6 4 5 21 I have tried this code but the problem is it adds up all the sums including the total so I get double the total e.g 42 should be 21 =SUM(Employee1!AF:AF) "Jacob Skaria" wrote: If you review the function.. =VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) The col_index_num is the column number within the table_array. Here there are only 28 columns in the table array $A$5:$AF$32. If you are looking to retrieve the last column of the above array it should be 28 instead of 32. =VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: vLookup Problem never ceases Dear someone I€„¢m having a problem with a vlookup formula, with (last cell/row always varying) what do I do to copy the last cell with data and paste it to another sheet. This formula below finds the first sum not the last. Or Is there another way to copy last cell with data and paste to another sheet? =IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE)) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117308 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What am I doing wrong
As Total is larger than other values in the range you could use:
=MAX(Employee1!AF5:AF32) Regards, Per "aussiegirlone" skrev i meddelelsen ... I opened a new Excel workbook testing the code but I get N/A As another solution to my problem is to do something Stupid like this.... in cell D5 on sheet where copy of total will be =SUM(Employee1!AF:AF), then in Cell C5 =Sum D5/2. although its double the trouble I am getting a true result, otherwise, I don't know what else to do. It seems strange to me as the vLookup use to work but I noticed that every few days when I cleared the data on employee sheet this caused the vlookup to become a #REF# Anyhow, I think I'll just let it go! Thankyou all very much for all your help I do appreciate it. "Jacob Skaria" wrote: OK. Let us try the below. With a table as below try the below formula which retrives the last matching value for 'test'. You can adjust this to suit for your requirement.. ColA ColB test 1 apple 32 test 2 apple 45 apple 52 boy 23 test 3 =LOOKUP(2,1/(A1:A10="test"),B1:B10) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: I dont understand why the codes you both have provided still returns 0 "Jacob Skaria" wrote: Oops...Sorry ...i would have been in another world while replying earlier.. Below mentioned is one way to retrieve the last value from AF for the matching valuue in Col A. 'array formula =INDIRECT(ADDRESS(MAX(IF(Employee1!$A$5:$A$32=A5,R OW(Employee1!$A$5:$A$32))),32)) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: Im sure I can count the number of columns and it is 32, however I copied your formula and it returns 0 so I change 28 to 32 and it returned the first sum instead of the last which is the total. 6 6 4 5 21 I have tried this code but the problem is it adds up all the sums including the total so I get double the total e.g 42 should be 21 =SUM(Employee1!AF:AF) "Jacob Skaria" wrote: If you review the function.. =VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) The col_index_num is the column number within the table_array. Here there are only 28 columns in the table array $A$5:$AF$32. If you are looking to retrieve the last column of the above array it should be 28 instead of 32. =VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: vLookup Problem never ceases Dear someone Im having a problem with a vlookup formula, with (last cell/row always varying) what do I do to copy the last cell with data and paste it to another sheet. This formula below finds the first sum not the last. Or Is there another way to copy last cell with data and paste to another sheet? =IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE)) |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What am I doing wrong
Note that for the below example the formula used is not VLOOKUP() but ***
LOOKUP() *** =LOOKUP(2,1/(A1:A10="test"),B1:B10) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: I opened a new Excel workbook testing the code but I get N/A As another solution to my problem is to do something Stupid like this.... in cell D5 on sheet where copy of total will be =SUM(Employee1!AF:AF), then in Cell C5 =Sum D5/2. although its double the trouble I am getting a true result, otherwise, I don't know what else to do. It seems strange to me as the vLookup use to work but I noticed that every few days when I cleared the data on employee sheet this caused the vlookup to become a #REF# Anyhow, I think I'll just let it go! Thankyou all very much for all your help I do appreciate it. "Jacob Skaria" wrote: OK. Let us try the below. With a table as below try the below formula which retrives the last matching value for 'test'. You can adjust this to suit for your requirement.. ColA ColB test 1 apple 32 test 2 apple 45 apple 52 boy 23 test 3 =LOOKUP(2,1/(A1:A10="test"),B1:B10) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: I dont understand why the codes you both have provided still returns 0 "Jacob Skaria" wrote: Oops...Sorry ...i would have been in another world while replying earlier.. Below mentioned is one way to retrieve the last value from AF for the matching valuue in Col A. 'array formula =INDIRECT(ADDRESS(MAX(IF(Employee1!$A$5:$A$32=A5,R OW(Employee1!$A$5:$A$32))),32)) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: Im sure I can count the number of columns and it is 32, however I copied your formula and it returns 0 so I change 28 to 32 and it returned the first sum instead of the last which is the total. 6 6 4 5 21 I have tried this code but the problem is it adds up all the sums including the total so I get double the total e.g 42 should be 21 =SUM(Employee1!AF:AF) "Jacob Skaria" wrote: If you review the function.. =VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) The col_index_num is the column number within the table_array. Here there are only 28 columns in the table array $A$5:$AF$32. If you are looking to retrieve the last column of the above array it should be 28 instead of 32. =VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: vLookup Problem never ceases Dear someone Im having a problem with a vlookup formula, with (last cell/row always varying) what do I do to copy the last cell with data and paste it to another sheet. This formula below finds the first sum not the last. Or Is there another way to copy last cell with data and paste to another sheet? =IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE)) |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What am I doing wrong
Simon,
I have spent quite some time on this "Horrendous " workbook to get it working without errors. so far so good; its 90 pages big so I'd rather not supply a complete Workbook as a sample as I now know it will not be deleted from discussion groups. But thank you for trying to help simon "Simon Lloyd" wrote: Aussiegirlone, can you supply a sample workbook? aussiegirlone;421660 Wrote: I dont understand why the codes you both have provided still returns 0 "Jacob Skaria" wrote: Oops...Sorry ...i would have been in another world while replying earlier.. Below mentioned is one way to retrieve the last value from AF for the matching valuue in Col A. 'array formula =INDIRECT(ADDRESS(MAX(IF(Employee1!$A$5:$A$32=A5,R OW(Employee1!$A$5:$A$32))),32)) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: Im sure I can count the number of columns and it is 32, however I copied your formula and it returns 0 so I change 28 to 32 and it returned the first sum instead of the last which is the total. 6 6 4 5 21 I have tried this code but the problem is it adds up all the sums including the total so I get double the total e.g 42 should be 21 =SUM(Employee1!AF:AF) "Jacob Skaria" wrote: If you review the function.. =VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) The col_index_num is the column number within the table_array. Here there are only 28 columns in the table array $A$5:$AF$32. If you are looking to retrieve the last column of the above array it should be 28 instead of 32. =VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: vLookup Problem never ceases Dear someone I€„¢m having a problem with a vlookup formula, with (last cell/row always varying) what do I do to copy the last cell with data and paste it to another sheet. This formula below finds the first sum not the last. Or Is there another way to copy last cell with data and paste to another sheet? =IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE)) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117308 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What am I doing wrong
Have you tried setting the last arg to TRUE
=VLOOKUP(A5,Employee1!$A$5:$AF$32,32,TRUE) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: Simon, I have spent quite some time on this "Horrendous " workbook to get it working without errors. so far so good; its 90 pages big so I'd rather not supply a complete Workbook as a sample as I now know it will not be deleted from discussion groups. But thank you for trying to help simon "Simon Lloyd" wrote: Aussiegirlone, can you supply a sample workbook? aussiegirlone;421660 Wrote: I dont understand why the codes you both have provided still returns 0 "Jacob Skaria" wrote: Oops...Sorry ...i would have been in another world while replying earlier.. Below mentioned is one way to retrieve the last value from AF for the matching valuue in Col A. 'array formula =INDIRECT(ADDRESS(MAX(IF(Employee1!$A$5:$A$32=A5,R OW(Employee1!$A$5:$A$32))),32)) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: Im sure I can count the number of columns and it is 32, however I copied your formula and it returns 0 so I change 28 to 32 and it returned the first sum instead of the last which is the total. 6 6 4 5 21 I have tried this code but the problem is it adds up all the sums including the total so I get double the total e.g 42 should be 21 =SUM(Employee1!AF:AF) "Jacob Skaria" wrote: If you review the function.. =VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) The col_index_num is the column number within the table_array. Here there are only 28 columns in the table array $A$5:$AF$32. If you are looking to retrieve the last column of the above array it should be 28 instead of 32. =VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: vLookup Problem never ceases Dear someone I€„¢m having a problem with a vlookup formula, with (last cell/row always varying) what do I do to copy the last cell with data and paste it to another sheet. This formula below finds the first sum not the last. Or Is there another way to copy last cell with data and paste to another sheet? =IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE)) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117308 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What am I doing wrong
yes you beauty Per Jessen you did it thank you! :):):)
"Per Jessen" wrote: As Total is larger than other values in the range you could use: =MAX(Employee1!AF5:AF32) Regards, Per "aussiegirlone" skrev i meddelelsen ... I opened a new Excel workbook testing the code but I get N/A As another solution to my problem is to do something Stupid like this.... in cell D5 on sheet where copy of total will be =SUM(Employee1!AF:AF), then in Cell C5 =Sum D5/2. although its double the trouble I am getting a true result, otherwise, I don't know what else to do. It seems strange to me as the vLookup use to work but I noticed that every few days when I cleared the data on employee sheet this caused the vlookup to become a #REF# Anyhow, I think I'll just let it go! Thankyou all very much for all your help I do appreciate it. "Jacob Skaria" wrote: OK. Let us try the below. With a table as below try the below formula which retrives the last matching value for 'test'. You can adjust this to suit for your requirement.. ColA ColB test 1 apple 32 test 2 apple 45 apple 52 boy 23 test 3 =LOOKUP(2,1/(A1:A10="test"),B1:B10) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: I dont understand why the codes you both have provided still returns 0 "Jacob Skaria" wrote: Oops...Sorry ...i would have been in another world while replying earlier.. Below mentioned is one way to retrieve the last value from AF for the matching valuue in Col A. 'array formula =INDIRECT(ADDRESS(MAX(IF(Employee1!$A$5:$A$32=A5,R OW(Employee1!$A$5:$A$32))),32)) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: Im sure I can count the number of columns and it is 32, however I copied your formula and it returns 0 so I change 28 to 32 and it returned the first sum instead of the last which is the total. 6 6 4 5 21 I have tried this code but the problem is it adds up all the sums including the total so I get double the total e.g 42 should be 21 =SUM(Employee1!AF:AF) "Jacob Skaria" wrote: If you review the function.. =VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) The col_index_num is the column number within the table_array. Here there are only 28 columns in the table array $A$5:$AF$32. If you are looking to retrieve the last column of the above array it should be 28 instead of 32. =VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: vLookup Problem never ceases Dear someone Im having a problem with a vlookup formula, with (last cell/row always varying) what do I do to copy the last cell with data and paste it to another sheet. This formula below finds the first sum not the last. Or Is there another way to copy last cell with data and paste to another sheet? =IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE)) |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What am I doing wrong
Alls well that ends well please there is no need for more replies thank you
all :) "Jacob Skaria" wrote: Have you tried setting the last arg to TRUE =VLOOKUP(A5,Employee1!$A$5:$AF$32,32,TRUE) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: Simon, I have spent quite some time on this "Horrendous " workbook to get it working without errors. so far so good; its 90 pages big so I'd rather not supply a complete Workbook as a sample as I now know it will not be deleted from discussion groups. But thank you for trying to help simon "Simon Lloyd" wrote: Aussiegirlone, can you supply a sample workbook? aussiegirlone;421660 Wrote: I dont understand why the codes you both have provided still returns 0 "Jacob Skaria" wrote: Oops...Sorry ...i would have been in another world while replying earlier.. Below mentioned is one way to retrieve the last value from AF for the matching valuue in Col A. 'array formula =INDIRECT(ADDRESS(MAX(IF(Employee1!$A$5:$A$32=A5,R OW(Employee1!$A$5:$A$32))),32)) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: Im sure I can count the number of columns and it is 32, however I copied your formula and it returns 0 so I change 28 to 32 and it returned the first sum instead of the last which is the total. 6 6 4 5 21 I have tried this code but the problem is it adds up all the sums including the total so I get double the total e.g 42 should be 21 =SUM(Employee1!AF:AF) "Jacob Skaria" wrote: If you review the function.. =VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) The col_index_num is the column number within the table_array. Here there are only 28 columns in the table array $A$5:$AF$32. If you are looking to retrieve the last column of the above array it should be 28 instead of 32. =VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE) If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: vLookup Problem never ceases Dear someone I€„¢m having a problem with a vlookup formula, with (last cell/row always varying) what do I do to copy the last cell with data and paste it to another sheet. This formula below finds the first sum not the last. Or Is there another way to copy last cell with data and paste to another sheet? =IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE)) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117308 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Insert Calculated Field (wrong Qty*Price = wrong Amount) | Excel Discussion (Misc queries) | |||
| Any wrong with this? | Excel Discussion (Misc queries) | |||
| i did something wrong | Excel Discussion (Misc queries) | |||
| Min Max What's Wrong? | Excel Worksheet Functions | |||
| What am I doing wrong? | Excel Discussion (Misc queries) | |||
Linear Mode
