Copying a formula verically that you want to increment horizontally

[Viper]

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$63) + COUNTIF('Main Inventory'!D$2$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$63 would solve this problem

Help!

 

[XL Guru]

>> =(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]

>> =(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.

 

[XL Guru]

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

eeek

sorry!

 

[Viper]

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]

putting in this

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

gives me the same output :con :fused:

 

[XL Guru]

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

 

[XL Guru]

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

 

[Viper]

see attached file for example. rename file extension as .xls and not "txt" (soz, had to do that to upload it.

 

[Viper]

{solved} thanks to XL Guru