Finds same row on same sheet or diffrent sheet.

[randomhouse]

Hello,
I'm trying to create a formula that will reference a diffrent line when a row is deleted above it.
Example:
I have a cell for example B7 in Sheet 1 that sees B7 on Sheet 2, and row 4 is deleted, it will still locate B7 on Sheet 2. The problem is I want the cell to now see B6 on Sheet 2.
Is there a fromula that will see only that particular row that it is on when changed?
I tried this formula =index(sheet2!A1:I28, row(), 4)
But this formula does not work when I have around a hundred that need referencing in this way.



The problem is when a row is deleted, it changes the parameters in the cell below

 

[Jonmo1]

Welcome to the board..

Can you clarify Which sheet a row is delted from?
The Sheet that contains the formula, or the sheet that the formula refers to?

 

[randomhouse]

Yes.

I'm working on the data sheet (sheet 1) and sheet 2 is the formula sheet.

The data sheet, I delete lines when they are no longer needed, and I need the rows that still exist to reference the same rows on the formula pages, but they stay the same, and will reference lines below.

Example:

A cell references B7 (sheet 2).
A line is deleted above it.
It still references B7.
But I now want it to reference B6 (sheet 2) formula page.

Can you please help?

 

[Jonmo1]

Sorry, that's just as clear as mud.

Please explain similar to this..

In Sheet1, I have a formula in Cell A10
=Sheet2!B7

If I delete Row4 from Sheet2, I want that formula in Sheet1 A10 to change to
=Sheet2!B6

 

[randomhouse]

Yes.

That's exactly what I want to do.

Example:

Sheet1, I have formula in Cell H12
=Sheet2!J12

Now when a row is deleted, like row 4 -

Sheet1 Cell H12 is now Cell H11
=Sheet2!J12

But I want Cell H11
=Shee2!J11



Sheet1, Cell H12
=Sheet2!J12

but I want it to =Sheet2!J11

I hope this is as clear as water than mud - ha ha

 

[Jonmo1]

So answer to my original question is
"I'm deleting a row from the sheet that contains the formula"...


You're on the right track with INDEX

for that example formula
=Sheet2!J12

Try
=INDEX(Sheet2!J:J,ROW())

 

[randomhouse]

I've tried it,

And it seems to be working.

This is great.

Thank you

Thank you

Thank you

I'm good with Excel, but the J:J without the numbers is genius.

 

[Jonmo1]

Glad to help, thanks for the feedback.