|
Stop Insert Row modifying formula data
I have a worksheet that grabs stock information via web queries. It always writes today’s information at the top of the page and to do this it I insert a row using VBA and set the cells to the correct values. The problem is that I have a number of other worksheets using the data from this stock information worksheet via lookups. And the insert row automatically alters their formulas from something like this: =VLOOKUP(A3,'Historical Funds'!$B$4:$C$3000,2,FALSE) To this =VLOOKUP(A3,'Historical Funds'!$B$5:$C$3001,2,FALSE) Which causes a problem as then all my lookups miss the top line of my array definitions. Any tips on how I can stop this? -- dazman ------------------------------------------------------------------------ dazman's Profile: http://www.excelforum.com/member.php...o&userid=25903 View this thread: http://www.excelforum.com/showthread...hreadid=393690 |
One way
=VLOOKUP(A3,INDIRECT("'Historical Funds'!$B$4:$C$3000"),2,FALSE) will always point to the same range, note that the workbook has to be open or else you'll get a ref error -- Regards, Peo Sjoblom (No private emails please) "dazman" wrote in message ... I have a worksheet that grabs stock information via web queries. It always writes today's information at the top of the page and to do this it I insert a row using VBA and set the cells to the correct values. The problem is that I have a number of other worksheets using the data from this stock information worksheet via lookups. And the insert row automatically alters their formulas from something like this: =VLOOKUP(A3,'Historical Funds'!$B$4:$C$3000,2,FALSE) To this =VLOOKUP(A3,'Historical Funds'!$B$5:$C$3001,2,FALSE) Which causes a problem as then all my lookups miss the top line of my array definitions. Any tips on how I can stop this? -- dazman ------------------------------------------------------------------------ dazman's Profile: http://www.excelforum.com/member.php...o&userid=25903 View this thread: http://www.excelforum.com/showthread...hreadid=393690 |
I read the OP a little differently then you do Peo.
I believe that when he said he misses the "top line", he wanted the range to *expand* with each row insertion. This anchors the "top" reference, and allows the range to expand downward: =VLOOKUP(A3,INDIRECT("'Historical Funds'!$B$4"):'Historical Funds'!$C$3001,2,FALSE) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Peo Sjoblom" wrote in message ... One way =VLOOKUP(A3,INDIRECT("'Historical Funds'!$B$4:$C$3000"),2,FALSE) will always point to the same range, note that the workbook has to be open or else you'll get a ref error -- Regards, Peo Sjoblom (No private emails please) "dazman" wrote in message ... I have a worksheet that grabs stock information via web queries. It always writes today's information at the top of the page and to do this it I insert a row using VBA and set the cells to the correct values. The problem is that I have a number of other worksheets using the data from this stock information worksheet via lookups. And the insert row automatically alters their formulas from something like this: =VLOOKUP(A3,'Historical Funds'!$B$4:$C$3000,2,FALSE) To this =VLOOKUP(A3,'Historical Funds'!$B$5:$C$3001,2,FALSE) Which causes a problem as then all my lookups miss the top line of my array definitions. Any tips on how I can stop this? -- dazman ------------------------------------------------------------------------ dazman's Profile: http://www.excelforum.com/member.php...o&userid=25903 View this thread: http://www.excelforum.com/showthread...hreadid=393690 |
Why not just use a dynamic named range?
Biff "Ragdyer" wrote in message ... I read the OP a little differently then you do Peo. I believe that when he said he misses the "top line", he wanted the range to *expand* with each row insertion. This anchors the "top" reference, and allows the range to expand downward: =VLOOKUP(A3,INDIRECT("'Historical Funds'!$B$4"):'Historical Funds'!$C$3001,2,FALSE) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Peo Sjoblom" wrote in message ... One way =VLOOKUP(A3,INDIRECT("'Historical Funds'!$B$4:$C$3000"),2,FALSE) will always point to the same range, note that the workbook has to be open or else you'll get a ref error -- Regards, Peo Sjoblom (No private emails please) "dazman" wrote in message ... I have a worksheet that grabs stock information via web queries. It always writes today's information at the top of the page and to do this it I insert a row using VBA and set the cells to the correct values. The problem is that I have a number of other worksheets using the data from this stock information worksheet via lookups. And the insert row automatically alters their formulas from something like this: =VLOOKUP(A3,'Historical Funds'!$B$4:$C$3000,2,FALSE) To this =VLOOKUP(A3,'Historical Funds'!$B$5:$C$3001,2,FALSE) Which causes a problem as then all my lookups miss the top line of my array definitions. Any tips on how I can stop this? -- dazman ------------------------------------------------------------------------ dazman's Profile: http://www.excelforum.com/member.php...o&userid=25903 View this thread: http://www.excelforum.com/showthread...hreadid=393690 |
Probably because it's easier (for me), to suggest a single formula.<bg
Lot more typing to describe how to create one (dynamic range), and the results are the same. Welllllll, almost the same. The Indirect() formula is a little more robust, taking the possibility of blank rows into consideration. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Biff" wrote in message ... Why not just use a dynamic named range? Biff "Ragdyer" wrote in message ... I read the OP a little differently then you do Peo. I believe that when he said he misses the "top line", he wanted the range to *expand* with each row insertion. This anchors the "top" reference, and allows the range to expand downward: =VLOOKUP(A3,INDIRECT("'Historical Funds'!$B$4"):'Historical Funds'!$C$3001,2,FALSE) -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Peo Sjoblom" wrote in message ... One way =VLOOKUP(A3,INDIRECT("'Historical Funds'!$B$4:$C$3000"),2,FALSE) will always point to the same range, note that the workbook has to be open or else you'll get a ref error -- Regards, Peo Sjoblom (No private emails please) "dazman" wrote in message ... I have a worksheet that grabs stock information via web queries. It always writes today's information at the top of the page and to do this it I insert a row using VBA and set the cells to the correct values. The problem is that I have a number of other worksheets using the data from this stock information worksheet via lookups. And the insert row automatically alters their formulas from something like this: =VLOOKUP(A3,'Historical Funds'!$B$4:$C$3000,2,FALSE) To this =VLOOKUP(A3,'Historical Funds'!$B$5:$C$3001,2,FALSE) Which causes a problem as then all my lookups miss the top line of my array definitions. Any tips on how I can stop this? -- dazman ------------------------------------------------------------------------ dazman's Profile: http://www.excelforum.com/member.php...o&userid=25903 View this thread: http://www.excelforum.com/showthread...hreadid=393690 |
Thanks for the replies - Sorted now! -- dazman ------------------------------------------------------------------------ dazman's Profile: http://www.excelforum.com/member.php...o&userid=25903 View this thread: http://www.excelforum.com/showthread...hreadid=393690 |
I usually say "Thanks For The Feed-back",
BUT ... Your feed-back is lacking in *exactly* what you used to "get it sorted".<bg -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "dazman" wrote in message ... Thanks for the replies - Sorted now! -- dazman ------------------------------------------------------------------------ dazman's Profile: http://www.excelforum.com/member.php...o&userid=25903 View this thread: http://www.excelforum.com/showthread...hreadid=393690 |
Hi RagDyer I ended up only needing the syntax to anchor the "top" reference in this instance because I can change the range with my VBA code. However after reading about the INDIRECT procedure I have used it a few time since in my forulas for this reason. Thanks! Daz -- dazman ------------------------------------------------------------------------ dazman's Profile: http://www.excelforum.com/member.php...o&userid=25903 View this thread: http://www.excelforum.com/showthread...hreadid=393690 |
| All times are GMT +1. The time now is 07:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com