I have an output from an engineering program that exports data into excel. I
would like to have a summary sheet where the pertinent data that I have can
be seen instead of having my clients having to pour over hundreds of sheets.
How do I get the text to appear.

Example

Cell A5 shows the text "A/V Alcove - 332"
Cell A105 shows the text "Accessible Entrance"

On another sheet, I want to put Name in the top row and below that, I want
to have it be able to call up Cell A5 and then have A105 below that. There
are hundreds of room names so an automatic way where I just pull down the
repeat would be awesome. I want the summary output to look like this.

Name
A/V Alcove - 332
Accessible Entrance

Offset does not work because it is pulling not automatically going down 100
cells to the next name to repeat, it is going to Cell A6 and then bringing
back the number with it.

Vlookup doesn't work for the same reason.

Thanks for your help.