get values from Sheet1 based on cell location in Sheet2 - dynamic address in a formula

In Excel I have a worksheet that has data in columns. Last Name ,First Name, Address,  Email, Phone Number.
Last Name: Smith
First Name:John
Address:15 Main Street
Email:Johnl@gmail.com
Phone Number:888 195-1234
Last Name: Jones
First Name:Bill
Address:25 South Street
Email:Bill@gmail.com
Phone Number:888 123-4567
Etc.
In a second sheet I have the formula  =RIGHT(Sheet1!A1,LEN(Sheet1!A1)-FIND(":",Sheet1!A1))  in cell B1   and =RIGHT(Sheet1!A2,LEN(Sheet1!A2)-FIND(":",Sheet1!A2)) in B2
etc for the first 5 columns

I want to take lines 6 through 10 and put the data in row 3.
I know I can write the formula  =RIGHT(Sheet1!A6,LEN(Sheet1!A6)-FIND(":",Sheet1!A6))   etc and so on for data in Sheet 1 rows 11-15 etc.

I tried cell references =RIGHT(INDIRECT("Sheet1!"&"A"&CELL("row")),LEN(INDIRECT("Sheet1!"&"A"&CELL("row")))-FIND(":",INDIRECT("Sheet1!"&"A"&CELL("row"))))

etc but it seems to jump all over depending on the last cell clicked on.
Is thera a way that it can use the actual cell as a reference?
So the data from sheet1 will be populated in sheet 2 with the description lines stripped out.
So in this example I will see:
Last Name   First Name    Address                 Email                         Phone Number
Smith               John            15 Main Street    Johnl@gmail.com      888 195-1234
Jones              Bill               25 South Street  Bill@gmail.com          888 123-4567

ETC.  I could have 25 to 100 or more contact names

Just trying to not have to cut / paste/ change column numbers   Repeat

Thanks
etc
LVL 11
Donald MaloneyConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Donald

Try this in A2 on Sheet2 and copy across/down, adjust the range for Sheet1 as required.

=MID(INDEX(Sheet1!$A$1:$Z$5,COLUMN(),ROW()-1),FIND(":",INDEX(Sheet1!$A$1:$Z$5,COLUMN(),ROW()-1))+1,255)
0
Donald MaloneyConsultantAuthor Commented:
Works for the first 5 rows in sheet 1 to the first 5 cells in row 2 of sheet 2 but
 not the rows 6 to 10 etc  from sheet 1.
On sheet 2 it just shows  #Value!  for all the other cells
0
NorieAnalyst Assistant Commented:
Donald

What exactly do you have on Sheet1 and how/where do you want it to appear on Sheet2?

Perhaps you could attach a sample workbook.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Donald MaloneyConsultantAuthor Commented:
Sheet1
Last Name: Smith
First Name:John
Address:15 Main Street
Email:Johnl@gmail.com
Phone Number:888 195-1234
Last Name: Jones
First Name:Bill
Address:25 South Street
Email:Bill@gmail.com
Phone Number:888 123-4567

Sheet2  (I put in row 1) should be
Last Name      First Name      Address                   Email                        Phone1
 Smith                  John       15 Main Street      Johnl@gmail.com      888 195-1234
 Jones                  Bill        25 South Street      Bill@gmail.com      888 123-4567

I was using the formula  for A2 in sheet 2  =RIGHT(Sheet1!A1,LEN(Sheet1!A1)-FIND(":",Sheet1!A1))

But I want the cell reference to be based on the cell the formula is in
So I don't have to type in  
A1 A2 A3 A4 A5
A6 A7 A8 A9 A10 etc.   into the formulas on sheet 2

If I copy and past excel  auto corrects the formula based on where it shows up in sheet2.

When I use CELL()  the formulas use the "Current" Cell reference on Sheet2 not the cell the formula is in.



So the data showing inSheet2
0
NorieAnalyst Assistant Commented:
Donald

So all the data in Sheet1 is in column A?
0
Donald MaloneyConsultantAuthor Commented:
Yes,
I'm also a real estate agent and get multiple leads on properties.
This is a new lead generator for me.
The data to extract comes in multiple emails but the main data is like lines 1-5.
I want to open the emails and copy/ paste the 5 lines into sheet 1. column 1.
 then I have the data cleaned for email addresses , move to contacts, etc.
0
NorieAnalyst Assistant Commented:
Donald

Try this in A2 on Sheet2, copy across to E2 and then copy  down.

=IFERROR(MID(INDEX(Sheet1!$A:$A,COLUMN()+(ROW()-2)*5),FIND(":",INDEX(Sheet1!$A:$A,COLUMN()+(ROW()-2)*5))+1,255),"")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Donald MaloneyConsultantAuthor Commented:
OK.  I was trying column/Row but you got it with adding them together.
Nice choice. works perfect.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.