Transfer text to another spreadsheet

[Guest]

Can I transfer text in one spreadsheet to another? Users choose one of three
worksheets to fill out and then the info should transfer to a form on another
sheet within the same workbook.

The formula ='Soil Conservationist'!D1, put within the form, worked to
transfer the info when I only had 1 worksheet for info, but I added the other
2 and it doesn't work. I need to be able to tell it to pull the info from
whichever of the 3 worksheets has been filled out. The names of the 3
worksheets are Soil Conservationist, Engineer, and Soil Scientist. One of
the cells I'm working on within them is cell D1, which is where they would
enter their name.

Susan

 

[Clivey_UK]

Susan,
Try this formula:
='Soil Conservationist'!D1&'Engineer'!D1&'Soil Scientist'!D1
This will put the results of all three sheets in one cell; if two ar
blank, then it would just show the result for just one that contain
data.
For further explanation of the way this works, if a formula said =A1&B
where A1="Jo Bloggs" and B1=3, the result would be Jo Bloggs3. If B1 wa
blank, the result would be Jo Bloggs.
Clive

 

[Guest]

Clive,
This didn't work. It didn't fill the name in from any of the worksheets.

Thank you for your reply.
Susan

 

[Pete_UK]

It may seem obvious, but did you have any data entered in any of the D1
cells?

Pete

 

[Clivey_UK]

Susan,
I've attached a 'Susan Example.jpg
(http://www.excelforum.com/attachment.php?attachmentid=4513&stc=1&d=1143124765)o
the test file I've set up. Can't see why it wouldn't work for you. I
the attached screenshot, D1 of each of the named sheets has the shee
name followed by a space. For instance, D1 of the Soil Conservationis
sheet shows 'Soil Conservationist '. The result is Soil Conservationis
Engineer Soil Scientist .
In your spreadsheet, only one sheet would have D1 filled in, so th
result would be say Engineer.
See the result of the formula and the actual formula above that.
Hope this helps.
Clive

Clive,
This didn't work. It didn't fill the name in from any of th
worksheets.

Thank you for your reply.
Susan

+-------------------------------------------------------------------
|Filename: susan Example.jpg
|Download: http://www.excelforum.com/attachment.php?postid=4513
+-------------------------------------------------------------------

 

[Guest]

Clive,
When I put the space in after the duty titles and entered, I got a box for
each of the worksheets, such as: "Update Value: Engineer", with file folders
as if it wanted me to rename something. I canceled all 3 and then #REF
showed up in the cell where the formula resides. What does it want now?

Thanks again.
Susan

 

[Clivey_UK]

Sorry Susan. I don't understand your first sentence after the firs
comma. Don't worry about the space after the duty titles; I only put i
there so the result wasn't Soil ConservationistEngineerSoil Scientist
you won't need the space. And in D1 you will have a name like Jo Blogg
rather than a title anyway.
You will get #REF if a formula refers to something that no longe
exists; for instance in my example, if I delete the Engineer sheet, th
formula returns #REF.
Please try to explain again the problems you experienced, specificall
'the box' you refer to, and 'file folders'.
Thanks
Clive
P.S. Or email me my yahoo.co.uk address: cliveyguard-shop@..
(Put the yahoo.co.uk in place of the ..)

 

[Guest]

It worked on the D1 cell info, except when I entered the formula for the next
block of info, changing the cell reference to D3, again the formula won't
work.

I can't seem to repeat the problem with the "box" I told you about. After I
deleted the spaces that you said I don't need, it immediately gave me what I
wanted to see.

 

[Clivey_UK]

Susan,
I'm not sure why it doesn't work in D3 when it works in D1; what resul
do you get - #REF? The formula in the Answer sheet (or whatever you hav
called it) should show:
='Soil Conservationist'!D3&'Engineer'!D3&'Soil Scientist'!D3
You mentioned moving to the next 'block of info'. Note this formul
will only work on individual cells, and not blocks.
For future postings, if anything doesn't give the result you want, jus
explain exactly what result it does give you.
e.g. I tried a similar formula in D3 of the Answer sheet with th
formula now saying ='Soil Conservationist'!D3&'Engineer'!D3&'Soi
Scientist'!D3 but an error message popped up saying "This doesn't work
error code 1234".

I suspect that whatever the problem it's something we can easil
solve.
Clive

 

[Guest]

Would the fact that the cell is a merged cell have anything to do with this?
Susan

 

[Guest]

Clive,
I noticed that the cell number format was set as Text instead of General.
After changing that, the update values box popped up again as the attachment
shows from a screen print. I again canceled for all 3 and now I get #REF
again, but I don't know what the problem is now.
Susan

 

[Guest]

Clive,
Sorry, I couldn't figure out how to attach a file to these posts.
Susan

 

[Guest]

Clive,
It helps if you spell Soil Scientist correctly. Everything is working great
now. I appreciate your help with this. I've been working on this for days.

Susan

 

[Clivey_UK]

Glad I could be of help Susan. A little tip I've found useful when
working with formulas that don't give the result you want; select one
part of the formula and press F9; Excel will change just that part of
the formula to be the result. Press Escape to undo the change, and then
try with the next part of the formula.
For instance in the formula ='Soil
Conservationist'!D1&Engineer!D1&'Soil Scientist'!D1, drag the mouse
over e.g. 'Soil Conservationist'!D1 and it changes the highlighted
section to be the value of that part of the formula. Trying this on
each of the three parts would give #REF for the 'Soil Sientist'!D1 so
you would know that's where the error lies. I don't think I've
explained it very well but try it out.
Clive
P.S. The mispelling explains why you saw the Update Values dialog box
you had previously mentioned. I tried mispelling it and got the same
result. The good thing though is that next time you see that Update
Values box, you'll know that it's because you're referring to something
that Excel doesn't recognize and can correct it.
P.P.S. One final tip: to avoid having to manually type too much and
therefore possibly having mispellings come into a formula, press = to
start a formula and then click the cell you want to reference. e.g. In
a blank cell of your answer sheet, press = and now click to go to
another sheet (say Engineer). Now click a cell and press Enter. You
will be taken back to the Answer sheet with the formula correctly
entered for you. In your example, in the Answer Sheet in say cell D1
you would press =, click the Soil Conservationist tab, click cell D1,
type &, click the Engineer tab, click cell D1, type &, click the Soil
Scientist tab, click cell D1, and press Enter. Try it out.