Excel Question

[nickhutt06]

Hello, so im trying to have a formula or whatever to make one cell take another cells formula BUT have a different column instead.
For example, my first cell has the total value of H1-H20 lets say. And i want my other cell to copy the first one but have I1-120 instead.
If someone can help and tell me if theres an easy way to do it that would be great! Thanks

 

[Joe4]

Welcome to the Board!

If you have the formula in cell H22 that is:

=H1-H20

If you were to copy that formula from H22 to I22, Excel will automatically change it to:

=I1-I20

for you.

The key thing here is where the cell you are "copying to" is in relation to where the cell you are "copying from" is.
The formula will automatically shift the rows and columns depending on how many rows/columns the new cell is from the original.

If the adjustments you want to make are arbitrary and follow no discernible pattern, I don't know that you are going to be able to do what you want without using VBA.

If you explain your actual data set-up and exactly what you are trying to do in detail, we may be able to make better suggestions.

 

[nickhutt06]

Welcome to the Board!

If you have the formula in cell H22 that is:

=H1-H20

If you were to copy that formula from H22 to I22, Excel will automatically change it to:

=I1-I20

for you.

The key thing here is where the cell you are "copying to" is in relation to where the cell you are "copying from" is.
The formula will automatically shift the rows and columns depending on how many rows/columns the new cell is from the original.

If the adjustments you want to make are arbitrary and follow no discernible pattern, I don't know that you are going to be able to do what you want without using VBA.

If you explain your actual data set-up and exactly what you are trying to do in detail, we may be able to make better suggestions.

Welcome to the Board!

If you have the formula in cell H22 that is:

=H1-H20

If you were to copy that formula from H22 to I22, Excel will automatically change it to:

=I1-I20

for you.

The key thing here is where the cell you are "copying to" is in relation to where the cell you are "copying from" is.
The formula will automatically shift the rows and columns depending on how many rows/columns the new cell is from the original.

If the adjustments you want to make are arbitrary and follow no discernible pattern, I don't know that you are going to be able to do what you want without using VBA.

If you explain your actual data set-up and exactly what you are trying to do in detail, we may be able to make better suggestions.

Thanks for the quick reply, i attached two images showing what i mean, if i complete the formula in image 1 i want image 2 to be the same thing but the column over if that makes sense.

 

[Joe4]

Those formulas aren't even referencing the same rows, so I don't see how you can do that!
One is referencing row 18 and the other row 19.

 

[nickhutt06]

Those formulas aren't even referencing the same rows, so I don't see how you can do that!
One is referencing row 18 and the other row 19.

Im not talking about the rows.. it doesnt matter what rows there in. Im saying i have a formula in a cell and want that same formula in another cell but to show a different column. And if theres a formula to automically do it

 

[Joe4]

Your formulas are referencing different columns too (it isn't all just one column).
Is there any sort of logic to the cells/rows that you are selecting?
Are you selecting things that follow some sort of pattern or meet some sort of criteria?
If so, you may be able to build a formula that builds in that logic, and then it can easily be applied to any range that you want.

But otherwise the only way I can think of to do strictly what you are asking would be to use VBA.

 

[nickhutt06]

Your formulas are referencing different columns too (it isn't all just one column).
Is there any sort of logic to the cells/rows that you are selecting?
Are you selecting things that follow some sort of pattern or meet some sort of criteria?
If so, you may be able to build a formula that builds in that logic, and then it can easily be applied to any range that you want.

But otherwise the only way I can think of to do strictly what you are asking would be to use VBA.

Yeah its for work. I manually select the Tons in the first picture to the customer (There's 2 different customers that use this data) And i have to manually select the Amount in the next column that are the exact same data just one column over. So my main question is "if i select the data in the first picture for the Tons, is there a formula that takes that formula and takes it in a new cell but just one column over. (Example: F1=H6+H7+H8,is there a formula to get another cell lets say P1 to = the next column over I6+I7+I8 automically without manually having to match it up)

 

[Joe4]

As I described in my first post, whatever cell you are placing your original formula in, if you copy and paste it EXACTLY one column over to the right, it will automatically do that for you.
I.e. if you are placing your original formula in cell O1, and copy it over to P1 it will do what you want.

Otherwise, I think you would have to get kind of creative with VBA, or "helper" cells, or doing a Find/Replace to replace one column reference with another.

 

[Eric W]

How are you selecting the rows to add? If you add a helper column where you mark the rows you want, you can do something like this:

Book1
	A	B	C	D	E	F	G
1				Page 1
2			Select	Tons	Amount
3
4			C	8.71	$ 362.51
5				8.22	$ 410.51
6			E	6.32	$ 263.04
7			C	7.15	$ 357.07
8			E	8.22	$ 342.12
9			C	8.22	$ 410.51
10			E	6.43	$ 267.62
11				9.73	$ 485.92
12			C	6.90	$ 344.59
13			E	9.75	$ 405.80
14				7.87	$ 393.03
15				5.83	$ 291.15
16			E	7.12	$ 296.33
17			C	10.30	$ 514.38
18				8.05	$ 402.02
19				4.91	$ 245.21
20	Tons		E	6.63	$ 275.94	Amount
21	EC	Coastal		7.64	$ 381.54	EC	Coastal
22	44.47	41.28				1850.85	1989.06
Sheet1

Cell Formulas
Range	Formula
A22	A22	=SUMIFS($D$3:$D$21,$C$3:$C$21,"E")
B22	B22	=SUMIFS($D$3:$D$21,$C$3:$C$21,"C")
F22	F22	=SUMIFS($E$3:$E$21,$C$3:$C$21,"E")
G22	G22	=SUMIFS($E$3:$E$21,$C$3:$C$21,"C")