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

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

Originally Posted by Susan

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

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

Susan,
I've attached a Susan Example.jpg 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

Originally Posted by Susan

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

Thank you for your reply.
Susan

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...=1143124765)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
>
>

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

Originally Posted by Susan

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