Unaligned table - cell references not staying lined up

Hi Everyone,

I am a uni student working on a large interrelated workbook for my honours thesis.

I am trying to duplicate and sort tables and am having trouble keeping the rows lining up and displaying data for the same variable (in this case trees as my project is on forestry).

In the first sheet called "Working" is where the data in the table is referenced too. The second sheet (DBH>30) is displaying a variety of data, and in the "value" column - only showing a value if cells in column c are of a certain size (in this case over 30). The cells here seem to be lined up fine. However, whenever I go to make a duplicate of this sheet (the third sheet - DBH 20-30) and change the formula in column "value" to only show sizes between 20 and 30 I run into the error.

All I change is the formula in the "value" column from =IF(C6>30,Working!AI6,0) to =IF(AND(C6>=20,C6<=30),Working!AI6,0)

You can see the difference in cell references between K18 and L18 on the third sheet. Eventually, I would like to be able to sort the third worksheet on descending "value" and then descending "dbhhob".

I have attached a sample workbook - my real one is 4000 rows long. The first worksheet is quite large, I was going to cut it down but I thought it needed to retain most of the cells because otherwise it wouldn't work to demonstrate the referencing error - sorry.

I am really appreciative on any assistance you may be able to offer me, thank you :D

Isn't the poblem caused by the fact that while in first sheet you refer to column B (not C as you mentioned) in the second you refer to C (as you described in text) ?

Your COUNTIF formula should be:

=COUNTIF(K6:K20,"r")

I don't understand your issue with alignment. Please mock up in the workbook a tab that shows what you want (do this manually).

Waiting for a response ...

OK - so a guess, then!

I used this:

=INDEX(Working!$B$2:$N$76,MATCH($A6,Working!$A$2:$A$76,0),MATCH(Table2356[[#Headers],[dbhob]],Working!$B$1:$N$1,0))

and this:

=IF(AND(B6>=20,B6<=30),INDEX(Working!$B$2:$N$76,MATCH($A6,Working!$A$2:$A$76,0),MATCH(Table2356[[#Headers],[Total FV ($)]],Working!$B$1:$N$1,0)),0)

on the third tab, where I also changed two of the column heading names to make this work. See if it resolves the issue.