1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Creating Formulas in rows from Data in columns

Discussion in 'Business Applications' started by Lemur, Apr 6, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. Lemur

    Lemur Thread Starter

    Joined:
    Apr 6, 2005
    Messages:
    40
    I have data in columns - starting A2 - P2 (and another set of data from A3-P3)

    I have various formulae using that data, starting in A20-A40.

    I wish to include the second 'row' of data (the A3-P3) into a second set of formulas from B20-B40.

    Filling 'right' doesn't work, because, if the formula in the A column is something as simple as "=A2+C2+D2", when I fill right, the formula in the B column is "=B2+D2+E2" - when I NEED it to read "=A3+C3+D3"

    I realize there is probably a simple workaround for this - in fact, I COULD just rekey the formula...the reasons I don't are 1) the formulas are more elaborate than I listed, 2) there will be many more rows of data (at least 15 more) - so, rekeying the formula for EACH row of data would be time-consuming and 3) it will eventually move the formulas off the same 'visible area' as the data (I'm currently combatting that by having the panes frozen).

    Is there any way to accomplish what I'm trying to get done?

    Chris
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Hi Chris. Difficult to say how much use any method(s) would be without sample data/formulas.

    One option: prefix =A2+C2+D2 with ' (apostrophe), then drag it across, then replace 2 with 3, then take out the 's.

    Rgds,
    Andy
     
  3. Lemur

    Lemur Thread Starter

    Joined:
    Apr 6, 2005
    Messages:
    40
    That's what I'm starting to think...there's no way to "fill" automatically. The formulas are much more elaborate than what I listed.

    I think I'm going to simply re-orient my data vertically. While not optimum (by a long shot), it will at least allow me to enter formulas ONCE and then, fill to the right for the results...

    Thanks!
     
  4. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Prefixing the column letters in the formulas ("A", "C", "E") with dollar signs will lock them for filling right ; but that won't help the row numbers. :(
     
  5. Lemur

    Lemur Thread Starter

    Joined:
    Apr 6, 2005
    Messages:
    40
    Exactly...

    OK - change focus (or, do I have to post a new topic?) :)

    Let's say I now have my DATA (20 rows or so) on the left side of the page, and I'm going to run my formula results (200 rows) on the right side of the page.

    Is there a way to FIX the data rows on the left side, so that, even while scrolling down on the right side, I'll always be able to access those data cells (for easier formula crafting?)

    It's not exactly 'freeze panes' - b/c I DO want to freeze the panes in a vertical fashion (i.e., to have a left side and right side; but I ONLY want to freeze the ability to move up and down on the left side of that screen. The right side needs to move up and down.

    This would alleviate a LOT of my frustration (which is why my data was oriented the way it was...)

    Chris
     
  6. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Open a new Window, then arrange them vertically or horizontally, whatever suits. You can view one fixed while moving around in the other.
     
  7. Lemur

    Lemur Thread Starter

    Joined:
    Apr 6, 2005
    Messages:
    40
    Cool - thanks....I just wanted to make sure it didn't cause my formula references to have to use an alternate workbook reference.

    If I select a cell from Window #2, and use it in a cell in Window #1 - then save #1, it's all fine, yes?

    If so - this will definitely work...thanks!

    Chris
     
  8. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> it's all fine, yes?

    Yup, it's just 2 instances of the exact same file. When you're done, close one window and then save to prevent it always opening as two windows. Enjoy.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/350281

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice