# Finds same row on same sheet or diffrent sheet.

#### randomhouse

##### New Member
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

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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?

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.

Sorry, that's just as clear as mud.

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

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

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())

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.

Glad to help, thanks for the feedback.

Replies
0
Views
83
Replies
5
Views
210
Replies
6
Views
328
Replies
2
Views
661
Replies
6
Views
270

1,203,242
Messages
6,054,350
Members
444,718
Latest member
r0nster

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back