Copying a formula verically that you want to increment horizontally

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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!
 
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
 

Viper

Thread Starter
Joined
Nov 5, 1999
Messages
366
XL Guru said:
>> =(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
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.
 
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
 

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
 

Viper

Thread Starter
Joined
Nov 5, 1999
Messages
366
putting in this

="'installed'!"&ADDRESS(ROW()+1-(ROW()-1),COLUMN()+ROW())&":"&ADDRESS((ROW()-(ROW())+63),COLUMN()+ROW())
gives me the same output :mad: :eek: :con :rolleyes: :confused: :fused:
 
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.
 
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
 

Attachments

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.
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top