| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
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 |
|
#8
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sort pages? | Excel Discussion (Misc queries) | |||
| trying to create an (almost) circular formula between cells and data validated cells with lists | Excel Worksheet Functions | |||
| Pivot table, dynamic data formula | Excel Discussion (Misc queries) | |||
| Import Data: on insert, shift data down and not right | Excel Discussion (Misc queries) | |||
| formula to determine the first column containing any data | Excel Worksheet Functions | |||
Linear Mode
