+ Reply to Thread
Results 1 to 7 of 7

Formula Added Only When Adjacent Cells Filled

  1. #1
    Registered User
    Join Date
    03-12-2010
    Location
    Milton, FL
    MS-Off Ver
    Excel 2003
    Posts
    4

    Formula Added Only When Adjacent Cells Filled

    Hello,

    I've been struggling with this problem for a little while. I noticed when I enter a formula in col C based on data in cols A and B, when I enter data in the next row in cols A and B, the formula copies below. This is controlled by the 'Extend list format and formula' in the Edit Options.

    The problem is that I don't always enter data consecutively. When I go back and enter the rest of the data, this messes up the auto-copy for the formula in col C. If it were just me usin this spreadsheet, no problem. But I'm designing it for multiple users. I'm trying to find a way to replicate the 'extend formula' feature of Excel synthetically (manually). Possible? (Here's another thread that communicated the issue but left it unresolved for my purposes: Other Thread.)

    Also, another potential solution. I don't need this spreadsheet to be very long. It tracks accounts for a year, and there usually are less than 100 entries. I've tried an IF function, leaving the true_condition blank (""). However, when the I print the sheet, it prints blank pages, because those 'blank' cells technically have data in them. If there's a way to not have those blank IF function cells print (which is a waste of paper and time), then that would provide a solution as well.

    Thank you in advance to anyone who can help!

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula Added Only When Adjacent Cells Filled

    if you set the print area first you wont have that problem
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    03-12-2010
    Location
    Milton, FL
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Formula Added Only When Adjacent Cells Filled

    That won't work. As I said, this is for multiple users. It would be much more difficult to teach all of them how to select the print area every time they want to print. And I can't set the print area myself because the length of the worksheets is variable. It needs to be a quick, brainless process; hence the need for something more automated. Thanks though.

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula Added Only When Adjacent Cells Filled

    difficult to teach all of them how to select the print area every time they want to print
    to be quite blunt they perhaps shouln't be using excel at all .this is one of the most basic things taught.
    how do they print?

  5. #5
    Registered User
    Join Date
    03-12-2010
    Location
    Milton, FL
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Formula Added Only When Adjacent Cells Filled

    Haha, very true. But we're talking people who are less than novices. Plus this is for a governmental entity, so we're also dealing with beaurocracy...To print they click the print button then click ok. Also keep in mind this will have to be used years into the future - even if I were to work with the current people, the procedure would be lost along the way I'm sure.

    That's why I'm looking for a basically fail-proof system that could be run by monkeys. The users should ONLY have to input data and click print. I'm good with Excel myself, and as I said I could do it on my own. But I'm not very experienced with designing spreadsheets for others (this would basically be a template). I just can't figure out how to create this scenario of a formula appearing only after the data is input, or at least not printing essentially blank cells because a formula technically exists. I have to admit though, I want to know this for my own knowledge as well. Whether or not the users could change the print area is only one consideration at this point. This is bothering me. It's the type of person I am.

  6. #6
    Registered User
    Join Date
    03-12-2010
    Location
    Milton, FL
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Formula Added Only When Adjacent Cells Filled

    So is there any way to synthetically create the scenario I'm trying to create?

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula Added Only When Adjacent Cells Filled

    Set the print area using a dynamic range. See http://www.contextures.com/xlNames01.html.
    Entia non sunt multiplicanda sine necessitate

+ 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