Avatar of Donald Maloney
Donald Maloney
Flag for United States of America asked on

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
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Donald Maloney

8/22/2022 - Mon
Norie

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 Maloney

ASKER
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
Norie

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Donald Maloney

ASKER
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
Norie

Donald

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

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Norie

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Donald Maloney

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