Hi,
I have some data which is not in a format which is useful for what I need it for
I do not want to manipulate the 'Raw' data in anyway, a I need to keep an audit trail. I do not wish to use a pivot table function, as I need to be able to drop new data into the 'Raw' data tab and an update to occur automatically.
=IFERROR(VALUE('Raw Data'!$D4:$L4,MATCH(INDEX('Raw Data'!$D$1:$L$1,MATCH(D$1,'Ra Data'!$D4:$L4,0))+1,'Raw Data'!$D$1:$L$1,0))),0)
I have been using the formula below to achieve the results I am after however, I am not happy with it, as it relies on the data being in the exact same order when new data is dropped into the 'Raw' data tab and this is not always the case. It also relies on the same number of data lines being present, when new data is dropped in and this is also not always the case.
I need a formula which has the same result as the above, but that matches against a column of unique codes and a row of unique codes (like an INDEX MATCH MATCH would). So that if the raw data shifts about. It will still populate the correct data against the correct unique codes in the reference column and row.
I know this may not make much sense, but I hope it makes sense to someone, who may be able to advise on a better formula to use.
TIA
Hannah
I have some data which is not in a format which is useful for what I need it for
I do not want to manipulate the 'Raw' data in anyway, a I need to keep an audit trail. I do not wish to use a pivot table function, as I need to be able to drop new data into the 'Raw' data tab and an update to occur automatically.
=IFERROR(VALUE('Raw Data'!$D4:$L4,MATCH(INDEX('Raw Data'!$D$1:$L$1,MATCH(D$1,'Ra Data'!$D4:$L4,0))+1,'Raw Data'!$D$1:$L$1,0))),0)
I have been using the formula below to achieve the results I am after however, I am not happy with it, as it relies on the data being in the exact same order when new data is dropped into the 'Raw' data tab and this is not always the case. It also relies on the same number of data lines being present, when new data is dropped in and this is also not always the case.
I need a formula which has the same result as the above, but that matches against a column of unique codes and a row of unique codes (like an INDEX MATCH MATCH would). So that if the raw data shifts about. It will still populate the correct data against the correct unique codes in the reference column and row.
I know this may not make much sense, but I hope it makes sense to someone, who may be able to advise on a better formula to use.
TIA
Hannah
