Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Result Change when Workbook Sent by E-mail
I've got a workbook with a couple of VLOOKUPs pointed to within the same
workbook. When I send the workbook via e-mail, the recipient gets different/incorrect values where ever the Vlookup formulas are when they open the file (punching F9 or any variation does nothing). When they send the workbook back to me and I open it, the VLOOKUP values are incorrect also and using any variation of F9 is fruitless, but if I go to the Raw Data page (where the VLOOKUPs are pointed to) and filter on it, suddenly the VLOOKUPs reflect the proper data--however, if the original recipient tries to do the same thing, the VLOOKUPs still show incorrect info. When I open the original workbook on my laptap, there are no issues--works like it should. I've sent hundreds of these kinds of the workbooks in the past and never had this issue. This was a uniquely created workbook (meaning I didn't cut/copy into an exisitng workbook). Any ideas? -- Rich F |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Result Change when Workbook Sent by E-mail
Is the VLOOKUP doing an exact match or an approximate match?
=VLOOKUP(LookUp_Table,Table_Array, Col_Index,[range_lookup]) if the optional [range lookup] is left empty or has a TRUE value the lookup does an exact match, or approximate match if an exact match is not found. Using a FALSE value for the optional range_lookup does an exact match only. -- Kevin Backmann "Rich F" wrote: I've got a workbook with a couple of VLOOKUPs pointed to within the same workbook. When I send the workbook via e-mail, the recipient gets different/incorrect values where ever the Vlookup formulas are when they open the file (punching F9 or any variation does nothing). When they send the workbook back to me and I open it, the VLOOKUP values are incorrect also and using any variation of F9 is fruitless, but if I go to the Raw Data page (where the VLOOKUPs are pointed to) and filter on it, suddenly the VLOOKUPs reflect the proper data--however, if the original recipient tries to do the same thing, the VLOOKUPs still show incorrect info. When I open the original workbook on my laptap, there are no issues--works like it should. I've sent hundreds of these kinds of the workbooks in the past and never had this issue. This was a uniquely created workbook (meaning I didn't cut/copy into an exisitng workbook). Any ideas? -- Rich F |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Result Change when Workbook Sent by E-mail
Kevin,
Thanks for the question--it's an exact match I'm looking for. Here's formula I'm using: =IF(ISNA(VLOOKUP($G41,'SD Targets'!$A$2:$C$87,2,FALSE)),"",VLOOKUP($G41,'SD Targets'!$A$2:$C$87,2,FALSE)) Wondering if using that clumsy IF(ISNA...) is making a difference? But I've used that countless times in the past and it's never been an issue (by the way, this is probably one of the smallest workbooks I've created in a while...only 154kb). Thanks again for your time! -- Rich F "Kevin B" wrote: Is the VLOOKUP doing an exact match or an approximate match? =VLOOKUP(LookUp_Table,Table_Array, Col_Index,[range_lookup]) if the optional [range lookup] is left empty or has a TRUE value the lookup does an exact match, or approximate match if an exact match is not found. Using a FALSE value for the optional range_lookup does an exact match only. -- Kevin Backmann "Rich F" wrote: I've got a workbook with a couple of VLOOKUPs pointed to within the same workbook. When I send the workbook via e-mail, the recipient gets different/incorrect values where ever the Vlookup formulas are when they open the file (punching F9 or any variation does nothing). When they send the workbook back to me and I open it, the VLOOKUP values are incorrect also and using any variation of F9 is fruitless, but if I go to the Raw Data page (where the VLOOKUPs are pointed to) and filter on it, suddenly the VLOOKUPs reflect the proper data--however, if the original recipient tries to do the same thing, the VLOOKUPs still show incorrect info. When I open the original workbook on my laptap, there are no issues--works like it should. I've sent hundreds of these kinds of the workbooks in the past and never had this issue. This was a uniquely created workbook (meaning I didn't cut/copy into an exisitng workbook). Any ideas? -- Rich F |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Result Change when Workbook Sent by E-mail
I don't see anything wrong about your formula at all. And when I did a
variation of yours it worked just fine. I did a lookup where the lookup column was not sorted in ascending order and another were it was sorted and in both cases everything worked as planned. I emailed myself the files, saved and opend them and nothing was wrong. Here's my version of the formula. The only differences are that I used A1 as the source value for the lookup instead of G41 and I was too lazy to recreate an 86 row lookup table and I settled for a 10 row one instead, other than that there aren't any changes: =IF(ISNA(VLOOKUP($A1,'SD Targets'!$A$1:$C$10,2,FALSE)),"",VLOOKUP($A1,'SD Targets'!$A$1:$C$10,2,FALSE)) I'm stumped... -- Kevin Backmann "Rich F" wrote: Kevin, Thanks for the question--it's an exact match I'm looking for. Here's formula I'm using: =IF(ISNA(VLOOKUP($G41,'SD Targets'!$A$2:$C$87,2,FALSE)),"",VLOOKUP($G41,'SD Targets'!$A$2:$C$87,2,FALSE)) Wondering if using that clumsy IF(ISNA...) is making a difference? But I've used that countless times in the past and it's never been an issue (by the way, this is probably one of the smallest workbooks I've created in a while...only 154kb). Thanks again for your time! -- Rich F "Kevin B" wrote: Is the VLOOKUP doing an exact match or an approximate match? =VLOOKUP(LookUp_Table,Table_Array, Col_Index,[range_lookup]) if the optional [range lookup] is left empty or has a TRUE value the lookup does an exact match, or approximate match if an exact match is not found. Using a FALSE value for the optional range_lookup does an exact match only. -- Kevin Backmann "Rich F" wrote: I've got a workbook with a couple of VLOOKUPs pointed to within the same workbook. When I send the workbook via e-mail, the recipient gets different/incorrect values where ever the Vlookup formulas are when they open the file (punching F9 or any variation does nothing). When they send the workbook back to me and I open it, the VLOOKUP values are incorrect also and using any variation of F9 is fruitless, but if I go to the Raw Data page (where the VLOOKUPs are pointed to) and filter on it, suddenly the VLOOKUPs reflect the proper data--however, if the original recipient tries to do the same thing, the VLOOKUPs still show incorrect info. When I open the original workbook on my laptap, there are no issues--works like it should. I've sent hundreds of these kinds of the workbooks in the past and never had this issue. This was a uniquely created workbook (meaning I didn't cut/copy into an exisitng workbook). Any ideas? -- Rich F |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Result Change when Workbook Sent by E-mail
Kevin,
Thanks again, sir, for your time and efforts. Like I said, this is the first time this has happened to me, so I'm thinking Excel must have hiccupped when I created this file because I just created another file from scratch exactly like the first one with updated raw data info and everything works fine, even when I e-mail it. Guess we can chalk it up to "sometimes applications have bad days...." Again, thanks for your time--have a great one! -- Rich F "Kevin B" wrote: I don't see anything wrong about your formula at all. And when I did a variation of yours it worked just fine. I did a lookup where the lookup column was not sorted in ascending order and another were it was sorted and in both cases everything worked as planned. I emailed myself the files, saved and opend them and nothing was wrong. Here's my version of the formula. The only differences are that I used A1 as the source value for the lookup instead of G41 and I was too lazy to recreate an 86 row lookup table and I settled for a 10 row one instead, other than that there aren't any changes: =IF(ISNA(VLOOKUP($A1,'SD Targets'!$A$1:$C$10,2,FALSE)),"",VLOOKUP($A1,'SD Targets'!$A$1:$C$10,2,FALSE)) I'm stumped... -- Kevin Backmann "Rich F" wrote: Kevin, Thanks for the question--it's an exact match I'm looking for. Here's formula I'm using: =IF(ISNA(VLOOKUP($G41,'SD Targets'!$A$2:$C$87,2,FALSE)),"",VLOOKUP($G41,'SD Targets'!$A$2:$C$87,2,FALSE)) Wondering if using that clumsy IF(ISNA...) is making a difference? But I've used that countless times in the past and it's never been an issue (by the way, this is probably one of the smallest workbooks I've created in a while...only 154kb). Thanks again for your time! -- Rich F "Kevin B" wrote: Is the VLOOKUP doing an exact match or an approximate match? =VLOOKUP(LookUp_Table,Table_Array, Col_Index,[range_lookup]) if the optional [range lookup] is left empty or has a TRUE value the lookup does an exact match, or approximate match if an exact match is not found. Using a FALSE value for the optional range_lookup does an exact match only. -- Kevin Backmann "Rich F" wrote: I've got a workbook with a couple of VLOOKUPs pointed to within the same workbook. When I send the workbook via e-mail, the recipient gets different/incorrect values where ever the Vlookup formulas are when they open the file (punching F9 or any variation does nothing). When they send the workbook back to me and I open it, the VLOOKUP values are incorrect also and using any variation of F9 is fruitless, but if I go to the Raw Data page (where the VLOOKUPs are pointed to) and filter on it, suddenly the VLOOKUPs reflect the proper data--however, if the original recipient tries to do the same thing, the VLOOKUPs still show incorrect info. When I open the original workbook on my laptap, there are no issues--works like it should. I've sent hundreds of these kinds of the workbooks in the past and never had this issue. This was a uniquely created workbook (meaning I didn't cut/copy into an exisitng workbook). Any ideas? -- Rich F |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a formula to change data result weekly. | Excel Worksheet Functions | |||
Change font and background color of several cells based on result of a formula | Excel Discussion (Misc queries) | |||
Protect the formula but change result. | Excel Worksheet Functions | |||
Is it possible to change the "result of a formula" to a "number? | Excel Discussion (Misc queries) | |||
Is it possible to change the "result of a formula" to a "number? | Excel Discussion (Misc queries) |