Using list of data in another worksheet

V

vanjoli

I am trying to take an imported text list of first and last names from one
worksheet and using =A2&", "&A1 on a second worksheet, I want to list all of
the names with a blank line between each of the names. There are too many
names to enter this formula each time. Whenever I try a series, it skips a
name. Any ideas how to easily do this?
 
S

SteveG

Use the formula you have for the first name. Starting with the second
name try this,

=OFFSET(A2,ROW(A1)+1,0)&", "&OFFSET(A1,ROW(A1)+1,0)

Copy down as needed.

Does that help?

Steve
 
V

vanjoli

Modified, this works for the second name, but as I copy it down, the
reference and the row values increment in the OFFSET function, which poses
the same issue as before. I may have had inaccurate info for the first
posting.

The formula for the first name, (cell B3), on the second worksheet actually
shows:
=Sheet1!C1&", "&Sheet1!B1

So for the second name, (in cell B5), I used:
=OFFSET(Sheet1!C1,ROW(A1)+0,0)&", "&OFFSET(Sheet1!B1,ROW(A1)+0,0)

And to get the third name, (in cell B7), I had to use:
=OFFSET(Sheet1!C1,ROW(A1)+1,0)&", "&OFFSET(Sheet1!B1,ROW(A1)+1,0)
 
V

vanjoli

Your information helped me enough to get it figured out. This is what I ended
up using:

=OFFSET(Sheet1!C3,-((ROW())/2+0.5),0)&",
"&OFFSET(Sheet1!B3,-((ROW())/2+0.5),0)
 
S

SteveG

Ok. I think I got it. Again, use your standard formula to retrieve the
first name from Sheet1! in B3. In B5 use:

=IF(ISEVEN(ROW()),"",OFFSET(Sheet1!$B$1,COUNTBLANK($B$3:B4),0)&",
"&OFFSET(Sheet1!$A$1,COUNTBLANK($B$3:B4),0))

You can then drag this down and it will only populate the next name in
every other row so in this case B7, B9, B11 and so on down the line.

Does that work for you?

Steve
 
Top