Excel formula?

J

jasonlife

New member
Aug 21, 2013
34
1
0
hey guys,
This has been driving me crazy for the last 2 hours. I need to have a formula to increment columns. But I should be able to reference another sheet as well.

For instance on sheet1, I have A5=4, F5=5, K5=443 etc. As you can see, these are multiples of 5 (the columns), now on sheet2 how do I add up these values by referencing the increment of 5?

Ideally I could have used the following on sheet2: =Sheet1!A5+Sheet1!F5, however I want to go beyond this and have the ability of incrementing the column letter by 5.

I try using the =Sheet1!address(1,column(sheet1!A5)+5) +Sheet1!address(1,column(sheet1!F5)+5) but this generates errors?

Any help appreciated.
 


Wut%20%2B_8c7b5cf477ed40a8124457a0a19ee376.jpg
 
You can solve this by using INDEX.

Example:
In Sheet1: A5=4, F5=5, K5=443


In Sheet2 put this formula in a cell: =INDEX(Sheet1!$5:$5, 1, COLUMN_NUMBER)

Set COLUMN_NUMBER to whatever column you want to reference to. A=1, B=2, and so on. The row in Sheet1 which you are reading from is defined by the matrix/vector you provide in the first argument and the row number of that matrix/vector, which you provide in the second argument.

See the Excel help reference for a more in-depth explanation of INDEX.
 
  • Like
Reactions: Staccs
knukk said:
You can solve this by using INDEX.

Example:
In Sheet1: A5=4, F5=5, K5=443


In Sheet2 put this formula in a cell: =INDEX(Sheet1!$5:$5, 1, COLUMN_NUMBER)

Set COLUMN_NUMBER to whatever column you want to reference to. A=1, B=2, and so on. The row in Sheet1 which you are reading from is defined by the matrix/vector you provide in the first argument and the row number of that matrix/vector, which you provide in the second argument.

See the Excel help reference for a more in-depth explanation of INDEX.
Click to expand...
Thanks, it did help me to some extent, but I'm stuck with one final part. How do I increment in excel. Basically like an i++ in java or something.

This is what I have so far '=INDEX('donttouchme'!$A$1:$AG$1,1,2+(8*(COLUMN(B1))))'
Basically, the range and row is correct, Im getting stuck with the column portion. It should increment the columns every 8 times (in the donttouchme sheet I have data in columns 2,10,18,26... basically 2 +(8*?)).. But how do I get ? to be an incremental function.. something like increment(1).. so that every successive column gets that incremented by 1 ????? This is driving me nuts!!!!
 
Another question is whether it is possible to skip out columns once merged. For instance if I have B+C+D as merged, then E+F+H as merged, then I+J+K as merged.

Now if in cell A1 I have 1, then in B1(which is a combination of B+C+D) I have 2, now I want to select and drag horizontally, but yet E1 (which is a combination of E+F+G) gives me 5, not 3- essentially since the columns are merged it still assigns values to C & D. How do I get it to avoid this and continue with the numbers sequentially ignoring all merged cells?
 
jasonlife said:
Another question is whether it is possible to skip out columns once merged. For instance if I have B+C+D as merged, then E+F+H as merged, then I+J+K as merged.

Now if in cell A1 I have 1, then in B1(which is a combination of B+C+D) I have 2, now I want to select and drag horizontally, but yet E1 (which is a combination of E+F+G) gives me 5, not 3- essentially since the columns are merged it still assigns values to C & D. How do I get it to avoid this and continue with the numbers sequentially ignoring all merged cells?
Click to expand...

Have you tried C++?
 
You must log in or register to reply here.
Top Bottom