We help IT Professionals succeed at work.

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

91 Views
Last Modified: 2018-09-25
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

NorieAnalyst Assistant
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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.
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
CERTIFIED EXPERT

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
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Donald MaloneyConsultant

Author

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