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.

Copying a formula verically that you want to increment horizontally

Discussion in 'Business Applications' started by Viper, Apr 6, 2004.

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

    Viper Thread Starter

    Joined:
    Nov 5, 1999
    Messages:
    366
    Hi

    I have an Excel spreadsheet containing company inventories.

    I have a sheet that has a list of all installed software. I have another sheet that calculates total installs (this has to be seperate for our auditing purposes).

    The first sheet has the sofware titles at the top, e.g. Office (column A), Oracle (Column B), PSP8 (Column C), etc and so on... with row headings being each computer we have's ID. eg PC01

    I transposed the column headings from the previous sheet into rows, so it now reads:

    Row1: Oracle
    Row2: Office
    Row3: PSP8

    etc, with the column headings on this sheet being "Licences held", "Installed", etc

    In the 2nd sheet (where I calculate values), I have the following formula:

    =(SUM('Main Inventory'!D$2:D$63) + COUNTIF('Main Inventory'!D$2:D$63,"Uninstal"))

    This formula has to be copied downwards. The range from 2 to 63 has to stay the same (hence why I used partial absolute addressing). However, the column letter needs to change from D to E to F and so on as it is copied down. As it is, it doesn't do this. I somehow thought that having it as D$2:D$63 would solve this problem :confused:

    Help!
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> =(SUM('Main Inventory'!d$2:d$63) +
    >> COUNTIF('Main Inventory'!d$2:d$63,"Uninstal"))

    1. What's the address of this cell?

    2. You need down to read
    =(SUM('Main Inventory'!E$2:E$63) + COUNTIF('Main Inventory'!E$2:E$63,"Uninstal"))

    , yes?

    Rgds,
    Andy
     
  3. Viper

    Viper Thread Starter

    Joined:
    Nov 5, 1999
    Messages:
    366
    1. that's irrelevant really. doesn't matter what cell it is in. i'm trying to perform that calculation in *ANY* cell, in a sheet that is NOT "Main Inventory".

    2. haven't you written exactly the same thing only replacing "D" with "e"?

    i have hundreds of rows, can't be doing this manually really.
     
  4. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> 1. that's irrelevant really

    No it isn't. Chill out, I'm just clarifying the parameters.

    Try this formula in E2 of 2nd sheet.

    ="'Main Inventory'!"&ADDRESS(ROW()+1-(ROW()-1),COLUMN()+ROW())&":"&ADDRESS((ROW()-(ROW())+63),COLUMN()+ROW())

    This should return 'Main Inventory'!$G$2:$G$63.

    Drag it down to E3 and E4, it should increment (columns) to
    'Main Inventory'!$H$2:$H$63
    'Main Inventory'!$I$2:$I$63

    Then tweak E2 to
    =SUM(INDIRECT("'Main Inventory'!"&ADDRESS(ROW()+1-(ROW()-1),COLUMN()+ROW())&":"&ADDRESS((ROW()-(ROW())+63),COLUMN()+ROW())))

    and drag over E3 and E4. Thus creating a "virtual range" to SUM.

    Rgds,
    Andy
     
  5. Viper

    Viper Thread Starter

    Joined:
    Nov 5, 1999
    Messages:
    366
    :confused: eeek

    sorry!
     
  6. Viper

    Viper Thread Starter

    Joined:
    Nov 5, 1999
    Messages:
    366
    ok, renamed 1st sheet as "installed" (soz) and turned on r1c1 mode.

    cell r2c3 in 2nd sheet currently reads:

    =(SUM(installed!R2C[1]:R63C[1]) + COUNTIF(installed!R2C[1]:R63C[1],"Uninstal"))

    and you want me to change it to (please be specific)? :s
     
  7. Viper

    Viper Thread Starter

    Joined:
    Nov 5, 1999
    Messages:
    366
    putting in this

    gives me the same output :mad: :eek: :con :rolleyes: :confused: :fused:
     
  8. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> sorry!

    No need. ½ the time I don't know how my brain works, so there's no reason why you should know. Also, R1C1 style is not needed.

    Basically you just need to bend the ROW and COLUMN functions to your will to create the required range references as text strings, then throw in INDIRECT to convert those strings to references you can actually work with.

    Quick example ; enter 1 - 2 - 3 in A1:A3 respectively. The formula

    ="A"&ROW()

    in B1 will return text string "A1". Copy down to return "A2" and "A3". Then changing them to

    =INDIRECT("A"&ROW())

    will convert them into working formulas.

    If you'd like a small example file, mail me at andy.j.brown
    @ntlworld.com

    and we could work on it from there.

    Rgds,
    Andy

    I wish we could go back to local time in here ; it's doing my head in.
     
  9. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Another option (an afterthought) which you may prefer.

    Select your software columns on Installed and CTRL+SHIFT+F3. Create Names in top row, OK.

    Then you can refer to these names on Sheet2. See specifically the formula bar for the selected cell (Sheet2!C2) in the screenshot. This formula can just be dragged straight down. Then check out the definitions of the names you created via Insert -- Name -- Define.

    Yep, I like that much better.

    Rgds,
    Andy
     

    Attached Files:

  10. Viper

    Viper Thread Starter

    Joined:
    Nov 5, 1999
    Messages:
    366
    see attached file for example. rename file extension as .xls and not "txt" (soz, had to do that to upload it.
     

    Attached Files:

  11. Viper

    Viper Thread Starter

    Joined:
    Nov 5, 1999
    Messages:
    366
    {solved} thanks to XL Guru (y) :cool:
     
  12. 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/217646

  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