Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'If cell empty, get data elsewhere' (2

    2003/SP2

    Dear Ex(cel)perts,

    I have a worksheet full of data but with some empty cells. For each of these empty cells I would like Excel to

    1. fill the empty cell with the data in an analogous position on a second worksheet, and

    2. add conditional formatting to indicate that this cell's data comes from the second worksheet, not the first.

    Is this possible? If so, is it also possible to first fill the empty cells on the second worksheet with data from a third? (and so on, though this particular project has only the three worksheets).

    Novice at VBA that I am, I'm hoping to do it with formulas instead, but will appreciate any solution.

    The data in question are all dates (in case it matters).

    Thanks in advance,
    Erik

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'If cell empty, get data elsewhere' (2

    If you want to use formulas, I'd use a new sheet to combine the results. It wouldn't be easy to use formulas in the blank cells on the data sheet. In the attached sample workbook, the sheet "End Results" shows the values from sheet A, and where those are lacking, values from sheet B, etc., up to sheet D.
    Conditional formatting is used to indicate whether the data are from sheet A or from another sheet. It uses a defined name (see Insert | Name | Define) since conditional formatting cannot refer directly to cells on another than the active sheet.
    Attached Files Attached Files

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 'If cell empty, get data elsewhere' (2

    This does not use conditional formatting, but makes them formulas.

    Select the range of interest
    edit - goto <special>
    select "Blanks" <ok>

    In what is now the active cell enter the equal sign (=) and then select the sheet where you want to read the values and select the analagous cell. While on the 2nd sheet and you have the cell selected hold <control> and press <enter>

    Then, while they are still selected, press the "Fill Color" icon and choose the color mark them.

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'If cell empty, get data elsewhere' (2

    Wow! At first look this is exactly what I need. Many thanks.

    HansV, you have always answered my questions (mostly about Word) not just correctly but within minutes (and from a faraway time zone, I'm pretty sure -- aren't you in Germany?). If you don't mind my asking, how on earth do you do it?

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'If cell empty, get data elsewhere' (2

    Thanks, Steve, I'll try that too.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'If cell empty, get data elsewhere' (2

    I'm in The Netherlands (see my profile).

    How do I do it? Once you start answering questions, you find that many of them share a common theme, so you can reuse (parts of) earlier replies. I have a reasonably good memory, that helps.

  7. #7
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'If cell empty, get data elsewhere' (2

    Oops, sorry to mix up Netherlands and Germany (guess that makes me a typical American!).

  8. #8
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'If cell empty, get data elsewhere' (2

    >>>>America

    Part of Canada, isn't it?

  9. #9
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'If cell empty, get data elsewhere' (2

    The extreme southern part. <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

Posting Permissions

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