+ Reply to Thread
Results 1 to 3 of 3

Linking to column in another Excel file + removing empty cells

  1. #1
    Registered User
    Join Date
    04-25-2013
    Location
    Haifa, Israel
    MS-Off Ver
    Excel 2007
    Posts
    5

    Linking to column in another Excel file + removing empty cells

    Hi all,
    I need your help in excel linking:
    Let's say I have 2 files: Source file and working file. In source file there are some text names in a column that are updated once in a while.
    1. I need to create a column in "working file" such that is taking values from column in "source file" even when "source file" is closed.
    2. It should take only non empty values, because I need to create a cell with validation list that consists of text names from the column.

    Solving attempt:
    By searching some solutions in forum I found that the first part I can do in the following way: copy column from "source file", select in "working file" a "paste special" option and choose "paste link". It works, but the problem is that it imports all the column: if in "source file" the column consists of words "a" (cell A1), "b" (cell A2) and all other cells in A column are empty - in "working file", after linking, it appears as "a" (cell A1), "b" (cell A2) and all other cells in A column are "0" (zeros) till cell A65536. And I need that in "working file" column after linking will appear as "a" (cell A1), "b" (cell A2) and all other cells will be empty, so by setting one of cells in B column to be a list (by "Data" - "Data validation" - "List" ) - it will consist only from "a" and "b", and not from "a", "b", "0", "0", "0", .... (65534 zeros).

    Any help is appreciated. Thanks!
    Last edited by LmdL; 07-15-2013 at 03:13 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Linking to column in another Excel file + removing empty cells

    You have two independent issues. One is to import or link to the data, and the other is to have a dynamically sized list for Data Validation.

    In Working File, use this formula to refer to each corresponding cell in Source File. It assumes both sets of data are in Column A.

    ='[Source File.xlsx]Sheet1'!A1&""

    Copy down for as many rows as desired. The &"" at the end forces conversion to a string so you don't get zeroes for blank cells.

    Now set up a named formula to provide the list. Under Formulas, Name Manager, add a name called DataList. The formula should look like this:

    =OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A:$A,"*?*"),1)

    DataList is now a list of nonblank elements in column A (this assumes no embedded blank cells).

    Then for your dropdown box, use Data Validation, List, and provide =DataList as the list reference.

    If any of this is not clear let me know and I can post a simple example.
    Last edited by 6StringJazzer; 07-15-2013 at 02:39 PM.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    04-25-2013
    Location
    Haifa, Israel
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Linking to column in another Excel file + removing empty cells

    Works excellent!
    Thank you very much!

+ 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. Replies: 2
    Last Post: 09-07-2012, 12:34 PM
  2. Linking to a column interspersed with empty cells?
    By Steve27 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-11-2010, 08:24 PM
  3. Conditional Formatting of blank/empty cells in Excel 2007
    By OverKnight in forum Excel General
    Replies: 4
    Last Post: 01-09-2009, 05:39 PM
  4. Handling Errors in Values & Empty cells in Excel 2007
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 11-28-2007, 06:37 AM
  5. linking with cells of others excel file
    By Omar Peru in forum Excel General
    Replies: 0
    Last Post: 05-08-2005, 04:06 PM

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