+ Reply to Thread
Results 1 to 16 of 16

Linking a special paste to a text cell

  1. #1
    Registered User
    Join Date
    09-10-2017
    Location
    Auckland,New Zealand
    MS-Off Ver
    2016
    Posts
    59

    Linking a special paste to a text cell

    Hey,

    I have an excel sheet that pastes data from another file in the format ='XXXX'!C10 etc. I then have a text field that is entered manually beside it and stored. The problem i have is that the C10 value in the original sheet changes from time to time (say what was C10 is now C11) and throws off the text field.

    Any thoughts?

    Thanks,

    Ad

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Linking a special paste to a text cell

    Attach a sample workbook showing what is happening and what you expect to happen.

  3. #3
    Registered User
    Join Date
    09-10-2017
    Location
    Auckland,New Zealand
    MS-Off Ver
    2016
    Posts
    59

    Re: Linking a special paste to a text cell

    I have uploaded documents in the past, but it wont let me do it anymore.

  4. #4
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Linking a special paste to a text cell

    Click Go Advanced then Manage Attachments then Upload.

  5. #5
    Registered User
    Join Date
    09-10-2017
    Location
    Auckland,New Zealand
    MS-Off Ver
    2016
    Posts
    59

    Re: Linking a special paste to a text cell

    I know how to but it wont let me =( i have contacted admin about it.

  6. #6
    Registered User
    Join Date
    09-10-2017
    Location
    Auckland,New Zealand
    MS-Off Ver
    2016
    Posts
    59

    Re: Linking a special paste to a text cell

    any thoughts on how to do this?

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,588

    Re: Linking a special paste to a text cell

    If you used the paperclip icon in the past, it no longer works, use the following method instead: Click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
    If you have already tried this then your file may be too large, reduce the size of the file to 1000 Kb at the most (a much smaller sample will probably be enough to demonstrate the problem).
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  8. #8
    Registered User
    Join Date
    09-10-2017
    Location
    Auckland,New Zealand
    MS-Off Ver
    2016
    Posts
    59

    Re: Linking a special paste to a text cell

    Hey,

    I think that worked, not to sure why it hasn't in the past as the file was not too large.

    Anyway, hope it helps clear up what i am trying to explain.

    Thanks,

    Adam
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    09-10-2017
    Location
    Auckland,New Zealand
    MS-Off Ver
    2016
    Posts
    59

    Re: Linking a special paste to a text cell

    Any thoughts?

  10. #10
    Registered User
    Join Date
    09-10-2017
    Location
    Auckland,New Zealand
    MS-Off Ver
    2016
    Posts
    59

    Re: Linking a special paste to a text cell

    no =( ????

  11. #11
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Linking a special paste to a text cell

    Sorry, I don't understand what the problem is. How is column C linked to column B? You say col C is manually entered, but you also say that it's 'thrown off' when something changes in cols A/B. Please give an example of what would be entered in column C, what's happening now which you don't want to happen, and what you want to happen.

    Edit after re-reading again:
    If you have a formula in C which is linking to B, but you want it to stay as it was entered even if B changes, then you can do this using an iterative circular formula, similar to what's used for timestamps on this page: https://chandoo.org/wp/2009/01/08/ti...-formula-help/
    Is that something like what you want, but with text instead of a timestamp?
    Last edited by Aardigspook; 04-10-2018 at 07:05 AM.
    Regards,
    Aardigspook

    Recently moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  12. #12
    Registered User
    Join Date
    09-10-2017
    Location
    Auckland,New Zealand
    MS-Off Ver
    2016
    Posts
    59

    Re: Linking a special paste to a text cell

    Hey,

    Thank you very much for your response.

    I think you may be on the right track but i am not sure it will work. Once C5 has a timestamp associated with it, no new entry that will go in to B5 (plum in this case) can get a new time stamp.

    The ultimate aim is to have a C text entry corresponding to the every populated cell in B. When B moves place i.e. from b4 to b5, i want the text entry in C to move with it and the new entry (plum) will have a new blank C value beside it. I was thinking something similar to a pivot table may work? i think i am close but cant get it to work.

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,588

    Re: Linking a special paste to a text cell

    If your entries were being made on sheet 1 they could be linked on sheet 2 using: =INDEX(Sheet1!B$2:B$9,ROW(1:1))
    Test by selecting cells B4:C4 on sheet 1 and (from the Home tab) select Insert > Insert Cells > Move Cells Down
    Type Plum into B4 and some text into C4 and look at sheet 2.
    Also test by selecting Delete (not the key) > Delete Cells > Move Cells Up and check that sheet 2 stays in order.
    Let us know if you have any questions.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    09-10-2017
    Location
    Auckland,New Zealand
    MS-Off Ver
    2016
    Posts
    59

    Re: Linking a special paste to a text cell

    Thanks for the reply Jet,

    Nearly there except the information is not entered on sheet 1, its entered on sheet 2. Once i know how to do this, sheet 1 and 2 will be separate workbooks.

    Sheet 1 (workbook 1) - Lets call this the driver that outputs a list of fruits. the order of which can change.

    Sheet 2 (workbook 2) - This is the workbook where we have the list of fruits and we want to input the color. The hard part is that the order of the data coming from sheet 1 can change.

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,588

    Re: Linking a special paste to a text cell

    What about a master list on another sheet? If there was a master list on sheet 3 then the following could be used to link the color to the fruit:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Test by inserting/deleting fruits on sheet 1 as described in post #13.
    Let us know if you have any questions.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    09-10-2017
    Location
    Auckland,New Zealand
    MS-Off Ver
    2016
    Posts
    59

    Re: Linking a special paste to a text cell

    Hey thanks for the reply,

    Yes thats a good idea but i still have the same problem as to how i get the list of fruits on to sheet 3 which is actually the same as sheet 2.

    I can look along the lines of manual exports and a vlookup to see what is missing from the sheet but this wouldnt be ideal.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA to paste special text
    By tacollier in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-24-2016, 09:57 PM
  2. Paste Special - Text
    By scotiatheone in forum Excel General
    Replies: 1
    Last Post: 06-21-2016, 06:20 AM
  3. pasting colored text doesn't paste via paste special
    By daqddyo1 in forum Excel General
    Replies: 2
    Last Post: 01-08-2015, 12:27 PM
  4. [SOLVED] how can I paste text using paste special, without clicking paste button?
    By Exxcel Noob in forum Excel General
    Replies: 6
    Last Post: 05-14-2012, 08:21 PM
  5. [SOLVED] Paste Special and Linking
    By ajkim001 in forum Excel General
    Replies: 8
    Last Post: 03-14-2006, 10:15 AM
  6. [SOLVED] Paste Special as Text
    By pkenchatt@britishlibrary.net in forum Excel General
    Replies: 0
    Last Post: 12-06-2005, 03:25 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1