reference to worksheet fails if row deleted

[GISJane]

I have 8 colums of information for one item in one row and I need to change
that to about 8 rows of information for the same item. Such as:
A B C
1 stuff junk other needs to become:

A
1 stuff
1 junk
1 other

there is other stuff involved, but this is the heart of the matter.

I have created a new spreadsheet that pulls all the information from the
first spreadsheet, exactly as I want it. I thought I would be able to pull
the information for the one item, then copy and paste as a value to another
spreadsheet, then go back to the first spreadsheet and delete the first row,
moving the second row to forst row position, and then the new spreadsheet
formulas would pull the information from the new first row in the first
spreadsheet.

But Excel, in all its wisdom, deletes in the formula worksheet the reference
to the first row (giving a #ref! error) when the first row in the first data
worksheet is deleted.

Is there a way to stop Excel from being so smart? If the formula
spreadsheet will just pull the data from the first row of the data worksheet,
even if the row is deleted and another row moves up, then I can accomplish my
task.

Any help is much appreaciated!!
Jane

 

[Sean Timmons]

If you're just trying to transpose the date, copy the entier table, go to
your new place, paste special/ transpose...

 

[GISJane]

Thanks for the suggestion, but I need to do more than that. The data table
has particular depths and the formula table needs to take the depth of one
cell and make it the 'TO' depth on one row and also use that same cell
reference on the next row as a 'FROM' depth. If I can just get Excel to
accept that it is to pull the data from row 1, whether or not row 1 has been
deleted and replaced, then I'm good to go.

Thanks for your suggestion. I had not used that command before and I'm glad
to see what it does!
Jane

 

[Sean Timmons]

use indirect.. =INDIRECT("A1")

Should ignore deletions and such.

 

[GISJane]

can Indirect be used within a formula that references another worksheet? I'm
not having luck with this, although it seems like this is the way I need to
go. The formula is: =Lithology!K2
but if the second row is deleted, then Excel is making the formula
=Lithology!#REF!

If the formula would just stay =Lithology!K2, my problem would be solved.

 

[Sean Timmons]

=INDIRECT("Lithology!$A$4")

Just need the " on either side...

 

[GISJane]

Bless you, Sean!! That seems to do the trick!!

Thank you so much!

 

[Sean Timmons]

Not a problem! Have fun with that!

Bless you, Sean!! That seems to do the trick!!

Thank you so much!