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

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

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

Originally Posted by Susan

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.

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

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

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

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

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.

Originally Posted by Susan

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