Transfer text to another spreadsheet
 

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

Transfer text to another spreadsheet
 

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 are
blank, then it would just show the result for just one that contains
data.
For further explanation of the way this works, if a formula said =A1&B1
where A1="Jo Bloggs" and B1=3, the result would be Jo Bloggs3. If B1 was
blank, the result would be Jo Bloggs.
Clive

Susan Wrote:
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
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=525465

Transfer text to another spreadsheet
 

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

Thank you for your reply.
Susan

"Clivey_UK" wrote:


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 are
blank, then it would just show the result for just one that contains
data.
For further explanation of the way this works, if a formula said =A1&B1
where A1="Jo Bloggs" and B1=3, the result would be Jo Bloggs3. If B1 was
blank, the result would be Jo Bloggs.
Clive

Susan Wrote:
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
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=525465

Transfer text to another spreadsheet
 

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


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

Thank you for your reply.
Susan




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

--
Clivey_UK
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=525465

Transfer text to another spreadsheet
 

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" wrote:


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


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

Thank you for your reply.
Susan




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

--
Clivey_UK
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=525465

Transfer text to another spreadsheet
 

Sorry Susan. I don't understand your first sentence after the first
comma. Don't worry about the space after the duty titles; I only put it
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 Bloggs
rather than a title anyway.
You will get #REF if a formula refers to something that no longer
exists; for instance in my example, if I delete the Engineer sheet, the
formula returns #REF.
Please try to explain again the problems you experienced, specifically
'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 ..)


Susan Wrote:
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
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=525465

Transfer text to another spreadsheet
 

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" wrote:


Sorry Susan. I don't understand your first sentence after the first
comma. Don't worry about the space after the duty titles; I only put it
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 Bloggs
rather than a title anyway.
You will get #REF if a formula refers to something that no longer
exists; for instance in my example, if I delete the Engineer sheet, the
formula returns #REF.
Please try to explain again the problems you experienced, specifically
'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 ..)


Susan Wrote:
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
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=525465

Transfer text to another spreadsheet
 

Susan,
I'm not sure why it doesn't work in D3 when it works in D1; what result
do you get - #REF? The formula in the Answer sheet (or whatever you have
called it) should show:
='Soil Conservationist'!D3&'Engineer'!D3&'Soil Scientist'!D3
You mentioned moving to the next 'block of info'. Note this formula
will only work on individual cells, and not blocks.
For future postings, if anything doesn't give the result you want, just
explain exactly what result it does give you.
e.g. I tried a similar formula in D3 of the Answer sheet with the
formula now saying ='Soil Conservationist'!D3&'Engineer'!D3&'Soil
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 easily
solve.
Clive

Susan Wrote:
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
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=525465

Transfer text to another spreadsheet
 

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

"Clivey_UK" wrote:


Susan,
I'm not sure why it doesn't work in D3 when it works in D1; what result
do you get - #REF? The formula in the Answer sheet (or whatever you have
called it) should show:
='Soil Conservationist'!D3&'Engineer'!D3&'Soil Scientist'!D3
You mentioned moving to the next 'block of info'. Note this formula
will only work on individual cells, and not blocks.
For future postings, if anything doesn't give the result you want, just
explain exactly what result it does give you.
e.g. I tried a similar formula in D3 of the Answer sheet with the
formula now saying ='Soil Conservationist'!D3&'Engineer'!D3&'Soil
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 easily
solve.
Clive

Susan Wrote:
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
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=525465

Transfer text to another spreadsheet
 

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

"Clivey_UK" wrote:


Susan,
I'm not sure why it doesn't work in D3 when it works in D1; what result
do you get - #REF? The formula in the Answer sheet (or whatever you have
called it) should show:
='Soil Conservationist'!D3&'Engineer'!D3&'Soil Scientist'!D3
You mentioned moving to the next 'block of info'. Note this formula
will only work on individual cells, and not blocks.
For future postings, if anything doesn't give the result you want, just
explain exactly what result it does give you.
e.g. I tried a similar formula in D3 of the Answer sheet with the
formula now saying ='Soil Conservationist'!D3&'Engineer'!D3&'Soil
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 easily
solve.
Clive

Susan Wrote:
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
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=525465

Transfer text to another spreadsheet
 

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

"Clivey_UK" wrote:


Susan,
I'm not sure why it doesn't work in D3 when it works in D1; what result
do you get - #REF? The formula in the Answer sheet (or whatever you have
called it) should show:
='Soil Conservationist'!D3&'Engineer'!D3&'Soil Scientist'!D3
You mentioned moving to the next 'block of info'. Note this formula
will only work on individual cells, and not blocks.
For future postings, if anything doesn't give the result you want, just
explain exactly what result it does give you.
e.g. I tried a similar formula in D3 of the Answer sheet with the
formula now saying ='Soil Conservationist'!D3&'Engineer'!D3&'Soil
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 easily
solve.
Clive

Susan Wrote:
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
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=525465

Transfer text to another spreadsheet
 

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" wrote:


Susan,
I'm not sure why it doesn't work in D3 when it works in D1; what result
do you get - #REF? The formula in the Answer sheet (or whatever you have
called it) should show:
='Soil Conservationist'!D3&'Engineer'!D3&'Soil Scientist'!D3
You mentioned moving to the next 'block of info'. Note this formula
will only work on individual cells, and not blocks.
For future postings, if anything doesn't give the result you want, just
explain exactly what result it does give you.
e.g. I tried a similar formula in D3 of the Answer sheet with the
formula now saying ='Soil Conservationist'!D3&'Engineer'!D3&'Soil
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 easily
solve.
Clive

Susan Wrote:
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
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=525465

Transfer text to another spreadsheet
 

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. :)

Susan Wrote:
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
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=525465