Formula linking to different Row&Column but Inconsistent.

[Roj47]

Good morning and Merry Christmas,

The below table is very simplified as the document I have has 10k+ rows.

I am currently clicking in the first green cell and typing the formula '=D3/3" but when I copy to the data to row10, I am moving the link manually up one level as the formula needs updating due to the additional row that I can;t move.

Is there a formula to capture the panel/ fence based on Unit as 'm' above so I am able to filter on 'Post' and copy throughout the 10k rows?

Thank you.

 

[t0ny84]

If I understand your question properly you want to copy and paste the value but not have the D3 portion change?

If this is the case then you can use Absolute References. This means you lock the column/row.

E.g.
$D$3 - this won't change either column or row.
$D3 - this locks the column but the row will change.
D$3 - this locks the row but the column will change.

t0ny84

 

[Roj47]

If I understand your question properly you want to copy and paste the value but not have the D3 portion change?

If this is the case then you can use Absolute References. This means you lock the column/row.

E.g.
$D$3 - this won't change either column or row.
$D3 - this locks the column but the row will change.
D$3 - this locks the row but the column will change.

t0ny84

Thank you so much for the correct answer but I didn;t ask the right question for my situation for which I apologise.

Happy new year and trust you had a great one.

I had meant to have the following:

Your answer is 100% correct but when applying to the table above, it wouldnt work for NY.

Under KY my formula is post=wood panel/5

Under NY the formula becomes Post = (wood panel + metal fence) /5

I currently insert the formula over multiple instances which sometimes leads to error as I ... 'switch off'.

Sorry again for my mistake in the original question.

 

[felixstraube]

Can you maybe share a bigger dataset?
And do you have those 2 formulas?:
1. Wood panel / 3
2. Post = (wood panel + metal fence) /5
And you automatically have to use one or the other depending on if there is a line that says "Metal Fence" or not?

How many lines of data can there be per group of data?

 

[Roj47]

Can you maybe share a bigger dataset?
And do you have those 2 formulas?:
1. Wood panel / 3
2. Post = (wood panel + metal fence) /5
And you automatically have to use one or the other depending on if there is a line that says "Metal Fence" or not?

How many lines of data can there be per group of data?

The data set would just be copy and paste of the above to generate the same query but taking up more space on the screen.

The formula would be in instance one (hand typed)

=C2/5

then in the second data set it would be

=(C6+C7)/5

I work through the lines manually entering the formula which is time consuming.

It depends on the document for how many lines but some instances are 10k lines so I manually enter a formula perhaps 1500+ times at worst.

Thanks.

 

[t0ny84]

Using something like below should help. This formula can be copied and pasted into each cell and it will check the value of the corresponding A cell for either KY or NY.

=IF(A3="KY", C2/5, IF(A3="NY", (C6+C7)/5, ""))

 

[felixstraube]

If you would share your whole dataset we could come up with better solutions to your problem.

 

[Roj47]

Using something like below should help. This formula can be copied and pasted into each cell and it will check the value of the corresponding A cell for either KY or NY.

=IF(A3="KY", C2/5, IF(A3="NY", (C6+C7)/5, ""))

Thank you - This works but would not hold up if a new location was added or if metal fence was added to KY.

With the start of the thread I am trying to tie to the unit 'm' along the lines.

Search for instances of 'm' in the group of items between blank rows, total and divide by 5 so if an update is added for 3 rows in a data set with m, it auto calculates the new total.

Appreciating the input so far thanks.

 

[Peter_SSs]

See if this gives you the results you want. I am assuming headers in row 2 as shown in post #1.

1. Filter column B for "Post"
2. In the first visible 'Post' row in column C put this formula, adjusted as described below it
   =(SUMIF(D$2:D4,"m",C$2:C4)-5*SUMIF(B$2:B4,"Post",C$2:C4))/5
   The red 2s should be the header row.
   The blue 4s should be the (hidden) row immediately above that first visible 'Post' row. So in my example I am entering this formula in cell C5
3. Copy that first formula cell, select all the rest of the way down column C to the bottom of your data and Paste
4. Remove the filter on column B