+ Reply to Thread
Results 1 to 7 of 7

Using an IF formula to copy text

  1. #1
    Registered User
    Join Date
    06-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Using an IF formula to copy text

    Hi All,

    I'm trying to use an IF function to have cells update between two workbooks but keep ending up with blank cells. For example:

    Book 1
    A B C
    501 Carly AA
    362 Robert AL
    441 Ted AA
    468 Mac AR


    Book 1 is the "master" copy and I want to link back to this from the new workbook. I want to be able to separate some people out (say Robert and Tim only) and have the corresponding information in column C (the text "AA" or "AL" or "AR") update whenever Book 1 is altered.


    I have tried using the formula:
    =IF(A1=Book1!$A:$A, Book1!C:C, " ")

    So, if the ID in column A matches the ID in book 1 the corresponding value in cell C for that row should be copied across. But all I end up with is blank cells all the time. Is this because I'm trying to use a formula to copy text? Or am I doing something else wrong?

    Any help is much appreciated!

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Using an IF formula to copy text

    try vlookup

    =vlookup(A1,refertothefirstbookselectABCColumn,2,false)
    something like this
    =vlookup('[Book1.xlsx]Sheet1'!$A$1:$C$10,2,false) -> the "2" is for column B, change to 3 for column C
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    06-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Using an IF formula to copy text

    Quote Originally Posted by vlady View Post
    try vlookup

    =vlookup(A1,refertothefirstbookselectABCColumn,2,false)
    something like this
    =vlookup('[Book1.xlsx]Sheet1'!$A$1:$C$10,2,false) -> the "2" is for column B, change to 3 for column C
    Thanks for your quick reply! I just wound up with a bunch of N/As =/
    I've attached a book with some of the data from the master copy (Book1). From this I'm trying to copy the entire row for Robert and Ted into a new workbook and have the values (the RR, AL, etc) update whenever the master copy is changed. Hope that makes sense! I suspect the problem is with the "IF" that I'm using as even if I change the text to numbers it still doesn't copy anything across.

    Book1.xlsx

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Using an IF formula to copy text

    Sorry but I can not give a formula for you.Since I think formulas will be a great deal as solution to your problem.
    You have 371 columns and don't know how many rows you have to be copied.

    Maybe a vba solution is better. Let's wait to other members on what they think in your case.

    Regards,
    Vladimir

  5. #5
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Using an IF formula to copy text

    Hi, Mcee,

    I placed Robert in A1 on the sheet and started with this formula in B1
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Drag across till NJ.

    Maybe use a dropdown accordiung to Data/Valkidation for the person in A1.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  6. #6
    Registered User
    Join Date
    06-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Using an IF formula to copy text

    Quote Originally Posted by HaHoBe View Post
    Hi, Mcee,

    I placed Robert in A1 on the sheet and started with this formula in B1
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Drag across till NJ.

    Maybe use a dropdown accordiung to Data/Valkidation for the person in A1.

    Ciao,
    Holger
    Thank you! I managed to swap about the cells it was referencing and the formula worked perfectly!

  7. #7
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Using an IF formula to copy text

    Hi, Mcee,

    glad you could make it work and thanks for the feedback/rep. This is kind of new experience for me in this Forum (being of any help with a formula while I normally take the "VBA-Train" in my answers).

    Ciao,
    Holger

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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