Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Beta 2TR Bug in Vlookup?
Hi,
In copying this formula: =VLOOKUP(E$42,IData!$E$3:$AY$65536,35,FALSE) from one column to another, regular copy and paste, it treats e$42 as if it is $e$42. If I copy and paste special, formulas and number formats, it will update the column reference correctly. Does anyone know if this is a known bug? Do you have this problem as well? Not sure where to post possible problems so thought I'd try here as well as mailing MS directly. Thank you, Deb |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Beta 2TR Bug in Vlookup?
I don't use xl2007, but are you sure you have calculation set to automatic?
Deb Plosk77 wrote: Hi, In copying this formula: =VLOOKUP(E$42,IData!$E$3:$AY$65536,35,FALSE) from one column to another, regular copy and paste, it treats e$42 as if it is $e$42. If I copy and paste special, formulas and number formats, it will update the column reference correctly. Does anyone know if this is a known bug? Do you have this problem as well? Not sure where to post possible problems so thought I'd try here as well as mailing MS directly. Thank you, Deb -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Beta 2TR Bug in Vlookup?
Hi Dave,
Thanks for your response. I'm at home (I have the Beta at work) and tested your idea on 2003. I have calculation off, but it still updates the formula. The value is not updated, but the formula reference correctly updates. Then when I hit calculate it calculates correctly. My issue is that in 2007 the formula itself is not updating, copying it from column E to D it still says e$42... "Dave Peterson" wrote: I don't use xl2007, but are you sure you have calculation set to automatic? Deb Plosk77 wrote: Hi, In copying this formula: =VLOOKUP(E$42,IData!$E$3:$AY$65536,35,FALSE) from one column to another, regular copy and paste, it treats e$42 as if it is $e$42. If I copy and paste special, formulas and number formats, it will update the column reference correctly. Does anyone know if this is a known bug? Do you have this problem as well? Not sure where to post possible problems so thought I'd try here as well as mailing MS directly. Thank you, Deb -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Beta 2TR Bug in Vlookup?
I entered your formula in Excel 2007 and did a regular copy and paste one
column to the right. The E$3 became F$3 as it should. Incidentally your formula could be written to compute more efficiently, particularly if you are going to copy it many times. The problem is that your formula creates a dependency on 47 columns when all it really depends on need is 2 (column E and column AM, the 35th): =INDEX($AM$3:$AM$65536,MATCH(E$42,IData!$E$3:$E655 36,FALSE)) If you do not have data going to row 65536 you might pick up efficiency by referencing the rows that will actually have data. -- Jim "Deb Plosk77" wrote in message ... Hi, In copying this formula: =VLOOKUP(E$42,IData!$E$3:$AY$65536,35,FALSE) from one column to another, regular copy and paste, it treats e$42 as if it is $e$42. If I copy and paste special, formulas and number formats, it will update the column reference correctly. Does anyone know if this is a known bug? Do you have this problem as well? Not sure where to post possible problems so thought I'd try here as well as mailing MS directly. Thank you, Deb |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Beta 2TR Bug in Vlookup?
Thanks Jim. Any other ideas as to what might be causing this? I've
replicated this several times, and just again now. Copying and pasting to the left or the right it does not update the reference, whether in manual or automatic calculation. The worksheet is unprotected. Still seems like it must be a bug unless I have some funky setting set . . . I'm working off someone else's code and am gradually replacing the VLookups for efficiency, as you mention ... I think the Index and Match idea is a good one for this sheet, thank you. (For some others I've been using Match and Offset). As we use this same spreadsheet every year with different amounts of data, referencing the exact # of rows where data is present has caused problems. Deb "Jim Rech" wrote: I entered your formula in Excel 2007 and did a regular copy and paste one column to the right. The E$3 became F$3 as it should. Incidentally your formula could be written to compute more efficiently, particularly if you are going to copy it many times. The problem is that your formula creates a dependency on 47 columns when all it really depends on need is 2 (column E and column AM, the 35th): =INDEX($AM$3:$AM$65536,MATCH(E$42,IData!$E$3:$E655 36,FALSE)) If you do not have data going to row 65536 you might pick up efficiency by referencing the rows that will actually have data. -- Jim "Deb Plosk77" wrote in message ... Hi, In copying this formula: =VLOOKUP(E$42,IData!$E$3:$AY$65536,35,FALSE) from one column to another, regular copy and paste, it treats e$42 as if it is $e$42. If I copy and paste special, formulas and number formats, it will update the column reference correctly. Does anyone know if this is a known bug? Do you have this problem as well? Not sure where to post possible problems so thought I'd try here as well as mailing MS directly. Thank you, Deb |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Beta 2TR Bug in Vlookup?
By the way I like this formula and tried it and it works great ... except to
copy it it doesn't update the column reference either (e.g., J$41 below, I manually edited to have J)! here it is now in column J: =INDEX(IData!$AI3:$AI4000,MATCH(J$41,IData!$E$3:$E $65536,0)) here it is when I copy from column J to K: =INDEX(IData!$AI3:$AI4000,MATCH(J$41,IData!$E$3:$E $65536,0)) So it's not just vlookup ... "Jim Rech" wrote: I entered your formula in Excel 2007 and did a regular copy and paste one column to the right. The E$3 became F$3 as it should. Incidentally your formula could be written to compute more efficiently, particularly if you are going to copy it many times. The problem is that your formula creates a dependency on 47 columns when all it really depends on need is 2 (column E and column AM, the 35th): =INDEX($AM$3:$AM$65536,MATCH(E$42,IData!$E$3:$E655 36,FALSE)) If you do not have data going to row 65536 you might pick up efficiency by referencing the rows that will actually have data. -- Jim "Deb Plosk77" wrote in message ... Hi, In copying this formula: =VLOOKUP(E$42,IData!$E$3:$AY$65536,35,FALSE) from one column to another, regular copy and paste, it treats e$42 as if it is $e$42. If I copy and paste special, formulas and number formats, it will update the column reference correctly. Does anyone know if this is a known bug? Do you have this problem as well? Not sure where to post possible problems so thought I'd try here as well as mailing MS directly. Thank you, Deb |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
printing of text box in excel 2007 beta | Excel Discussion (Misc queries) | |||
how do I get a formual to stay constant in vlookup in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2007 Beta - can't edit macros | Excel Discussion (Misc queries) | |||
VLOOKUP function in Excel 97 | Excel Worksheet Functions |