Prev: Can i get the Email
Next: Can offset be used in this formula?
From: P Alicea on 8 Mar 2010 09:45
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.
From: Max on 8 Mar 2010 10:59
You can use OFFSET. Here's how ..
Assume your source sheet is named: Sheet1
In your summary sheet,
you could put this in any startcell, say in B2:
=OFFSET(Sheet1!A$5,ROWS($1:1)*100-100,)
This returns the same as the simple link: =Sheet1!A5. But when you copy B2
down, it'll increment to return the desired: =Sheet1!A105 in B3, and so on.
Success? hit YES below
--
Max
Singapore
---
"P Alicea" wrote:
> 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.
 | 
Pages: 1
Prev: Can i get the Email
Next: Can offset be used in this formula?