I have a standard sheet formatted like this, with columns going linearly

First Name Last Name Contact Type Address Address 2 City State

My goal is to take the data in this sheet and format it differently in another sheet, like this, vertically

First Name Last Name

Contact Type

Address

Address 2

City State Zip

For the users, they can then use this to print out mailing labels (plus for some of my users, reading info in block form is just easier)

So I can do something like this in the new sheet

={NameDirectory!B4&" "&NameDirectory!C4}

={NameDirectory!D4}

={NameDirectory!E4&" "&NameDirectory!F4}

etc, so each line grabs the right data, so the address appears in a block

Of course, the next block will be similar

={NameDirectory!B5&" "&NameDirectory!C5}

={NameDirectory!D5}

={NameDirectory!E5&" "&NameDirectory!F5}

The only thing that changes is the number as I have merely shifted down a row.

I keep thinking there must be an easier way to do this, other than copying the formula, pasting it in and manually changing B5 to E5. Likewise, I can drag the formula down multiple rows, but it changes the number not the letter as I want.

Is there anyway I can achieve what I want without a lot of copying and pasting?

Here is an example (not sharing the original as it as real contact information, so this has two entries, but the original has around 50 )