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

Donald Maloney
Donald Maloney used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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)
Donald MaloneyConsultant

Author

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
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Donald MaloneyConsultant

Author

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
NorieAnalyst Assistant

Commented:
Donald

So all the data in Sheet1 is in column A?
Donald MaloneyConsultant

Author

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.
Analyst 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),"")
Donald MaloneyConsultant

Author

Commented:
OK.  I was trying column/Row but you got it with adding them together.
Nice choice. works perfect.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial