Setting Up Cell References that remain even after source cells deleted

[Joshua McNichols]

Hello,

I would like to setup cell references that will survive deletion.

For example, I have a specially formated cell (let's call it A1 on Sheet 'Daily_Printout'!) that references a cell H4 on sheet 'Database'!

Every day, I delete Row H, and that cell will be automatically populated by the row below. The values all across row H will change. I would like my 'Daily_Printout' A1 cell to reference whatever appears in 'Database'!
cell H4, regardless of the history of what was formerly in that cell, or whether those new values were automatically populated when the original cell was deleted. How can I reference cells in this way?

This is an important step in my creating a database of radio stories whose selected daily rundown can be printed out in a more readable "rundown" for radio hosts.

 

[Jonmo1]

Try

=INDEX('Database'!H:H,4)

 

[Firefly2012]

Hi

There are mutliple ways to do this eg:

=INDIRECT("'Database'!H4")

or

=INDEX(Database!H:H,4)

 

[Joshua McNichols]

Thank you, INDIRECT worked. INDEX required access to an external file.

 

[Jonmo1]

INDEX required access to an external file.

I dont understand what you mean by this.

you're saying that this works
=INDIRECT("'Database'!H4")

But this doesn't
=INDEX(Database!H:H,4)

What exactly happens when you try the Index function?

 

[Joshua McNichols]

When I try to exit the cell so that the formula will apply, a dialogue box opens so that I can browse for another excel file on my computer. It seems that it expects the index to be external to my workbook.

But you needn't worry about it, as INDIRECT resolved my problem.

 

[Jonmo1]

That indicates that there is no sheet named Database in the book.

Are you deleting and recreating the SHEET as well as the Rows?

 

[Joshua McNichols]

Oh, I see. I was not deleting the sheet - I must have failed to replace my placeholder name Database with the real sheet name when I inserted the formula. That would explain it. I used "Database" here because the title helps explain how I use that sheet.

 

[Jonmo1]

OK, good. That just wasn't sounding right. Glad that's straightened out.


FYI, the INDEX method would be preferred. Particularly if there will be alot of formulas using this functionality.

Indirect is a volatile function, meaning it recalculates every time anything changes on the sheet.
Even if that change is not related to the formula in any way.