Formula - Shifting by Row

stumped · May 14th, 2002, 11:32 AM

In my workbook, I have a database that holds a bunch of information. When the user fills in a worksheet, they click a macro button that goes to the database worksheet and inserts a row and fills in the information. On another tab, I have a the following formula written so that the user can retrieve the information they put in. But every time the user puts in new information it changes my formula. I have tried to put $ before the column and row and it still changes the starting row.
What am I doing wrong?

=INDEX(database!N2:N990,MATCH(L4,database!B2:B990)) (I want this to stay permanent)

Each time the user inputs new information into the database it shifts it down a row N2 to N3 adn B2 to B3.

=INDEX(database!N3:N990,MATCH(L4,database!B3:B990))

Help!

Jay Petrulis · May 14th, 2002, 01:07 PM

Quote:

On 2002-05-14 10:32, stumped wrote:
In my workbook, I have a database that holds a bunch of information. When the user fills in a worksheet, they click a macro button that goes to the database worksheet and inserts a row and fills in the information. On another tab, I have a the following formula written so that the user can retrieve the information they put in. But every time the user puts in new information it changes my formula. I have tried to put $ before the column and row and it still changes the starting row.
What am I doing wrong?

=INDEX(database!N2:N990,MATCH(L4,database!B2:B990)) (I want this to stay permanent)

Each time the user inputs new information into the database it shifts it down a row N2 to N3 adn B2 to B3.

=INDEX(database!N3:N990,MATCH(L4,database!B3:B990))

Help!

Hi,

I'm not sure what exactly is happening -- when the rows are getting shifted, etc. -- but one workaround is to overwrite the formula as the *last* line of code.

"=INDEX(Database!R2C14:R990C14,MATCH(R4C12,Database!R2C2:R990C2))"

So, whatever happens to your formula, it gets replaced with what you want.

Let us know how this works for you.

Bye,
Jay

WayneTN · May 14th, 2002, 07:49 PM

You may need checkout the INDIRECT function. Plus, there are other posts here that describe how to add new data to a worksheet and have named ranges extend/expand (if that would be helpful in designing your worksheet).

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 14th, 2002, 11:32 AM	#1
stumped New Member Join Date: Jan 2002 Posts: 9	In my workbook, I have a database that holds a bunch of information. When the user fills in a worksheet, they click a macro button that goes to the database worksheet and inserts a row and fills in the information. On another tab, I have a the following formula written so that the user can retrieve the information they put in. But every time the user puts in new information it changes my formula. I have tried to put $ before the column and row and it still changes the starting row. What am I doing wrong? =INDEX(database!N2:N990,MATCH(L4,database!B2:B990)) (I want this to stay permanent) Each time the user inputs new information into the database it shifts it down a row N2 to N3 adn B2 to B3. =INDEX(database!N3:N990,MATCH(L4,database!B3:B990)) Help!

May 14th, 2002, 07:49 PM	#3
WayneTN Board Regular Join Date: May 2002 Location: Tennessee, USA Posts: 72	You may need checkout the INDIRECT function. Plus, there are other posts here that describe how to add new data to a worksheet and have named ranges extend/expand (if that would be helpful in designing your worksheet).