In Excel I have a worksheet that has data in columns. Last Name ,First Name, Address, Email, Phone Number.
Last Name: Smith
Address:15 Main Street
Phone Number:888 195-1234
Last Name: Jones
Address:25 South Street
Phone Number:888 123-4567
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