multi sheet formula

[djw76]

Hi there,

I have been looking around but cannot find a similar thread. If there is one I apologize for the duplicate.
I have a spreadsheet that has demographic data on sheet1 and goes for 12 pages. I have sheet 2 that goes into more detail and want excel to automatically pull some info from sheet1.
ie - D2:M2 have name (first & last together), then again in D54:M54 & D106:106 etc...
On sheet2 I need it to take the name and split it into 2 cells (sheet2's A2 - first name from sheet1's D2, sheet2's B2 - last name from sheet1's D2)

I've done something like this years ago, but cannot remember how to build it...

Thanks

 

[SteveO59L]

How are the Forename/Surname seperated?

 

[nightcrawler23]

See this thread.

the user has used formulas to separate two words divided by space.

http://www.mrexcel.com/forum/showthread.php?t=561856

 

[djw76]

How are the Forename/Surname seperated?

the names are separated by a space (ie. John Smith)

 

[djw76]

See this thread.

the user has used formulas to separate two words divided by space.

http://www.mrexcel.com/forum/showthread.php?t=561856

Thanks for the point... but I'm sorry to say that I don't quite understand it
I tried using the formulas (and, to the best of my ability, modifying for my exercise) and get '#REF!'

 

[nightcrawler23]

Try This,

A1 = Night Crawler
B1 =LEFT(A1,FIND(" ",TRIM(A1),1))
C1 =TRIM(SUBSTITUTE(A1,B1,"",1))

 

[djw76]

Try This,

A1 = Night Crawler
B1 =LEFT(A1,FIND(" ",TRIM(A1),1))
C1 =TRIM(SUBSTITUTE(A1,B1,"",1))

Hi there,

This is GREAT!! thank you.
A couple of things though....
The second formula (C1) just gave me both first and last name again. I tried using the first one and substituting LEFT with RIGHT but it didn't quite work. I got some extra letters from the first name or for long last names I missed the 1st few letters.

Second... with the layout of my form, is there a way to sequence the formula? I've tried dragging the corner down but it sequences D2, D3, D4 etc. I need it to do D2, E2, F2, G2:M2, then jump to D54, E54 etc.

Thanks again for your help

 

[nightcrawler23]

I dont actually understand your problem. Coul;d you post some of your data here.

 

[nightcrawler23]

Hi there,

The second formula (C1) just gave me both first and last name again.

Can you post the name and the formulas you used. It works for me.

 

[djw76]

(Sheet1)
A B C D E F ........ M
1'text' 'text' 'text'
2'text' 'text' 'text' Johnny Depp Night Crawler Bob Smith
3
.
.
~repeats @ 53

53'text' 'text' 'text'
54'text' 'text' 'text' Jack Sparrow Bob Smith Charlize Theron
55
.
.
~repeats again (total - 12 times)

(Sheet2)
A B ....
1 First Name Last Name
2 =LEFT(Sheet1!D2,FIND(" ",TRIM(A1),1)) =RIGHT(Sheet1!D2,FIND(" ",TRIM(A1),1))
3
.
.
.

What's happening is, on Sheet2, in A2 - what I'm getting is the first name (but I get a little bit of the last name if the first name is short). Then in B2 - I'm getting some of the first name if the last name is short or missing letters from the last name if it is long.

Next, I need to be able to copy the formula down to copy/split the names for each page from Sheet1


I hope this all makes sense

Thanks again