Link to home
Start Free TrialLog in
Avatar of Donald Maloney
Donald MaloneyFlag 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
Avatar of Norie
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)
Avatar of 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
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.
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
Donald

So all the data in Sheet1 is in column A?
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.
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
OK.  I was trying column/Row but you got it with adding them together.
Nice choice. works perfect.