Hi
I have, with the amazing help with somebody on this forum, managed to come close to getting a formula to work but I cannot put the final pieces together. I have the following formula
=INDEX(CCPInventory!$A$1:$Z$5000,MATCH(O29,CCPInventory!B:B,0),MATCH("VAR_CostPrice_Net",CCPInventory!$A$1:$Z$1,0))
The problem with this is that the O29 and the CCPInventory!B:B are hardcoded which wont work. The formulas I have are:
=INDEX($A$1:$DD$5000,0,MATCH("External ID",$A$1:$Z$1,0)) returns the correct value for O26 but if I copy and paste this formula over the O26 in the first formula I get an #N/A
and
=MATCH("VAR_SKU",CCPInventory!$A$1:$Z$1,0) which returns 2. Firstly this range is on another sheet "CCPInventory" and secondly I have tried using the address function to give me the column letter but this gives me the address of a cell and not the entire column.
This entire formula has no explicit references since all cols are constantly in different places so I have to create the entire statement based on the column headers hence the reason for so many match statements.
Any and all help would be appreaciated.
Thanks
Steve
I have, with the amazing help with somebody on this forum, managed to come close to getting a formula to work but I cannot put the final pieces together. I have the following formula
=INDEX(CCPInventory!$A$1:$Z$5000,MATCH(O29,CCPInventory!B:B,0),MATCH("VAR_CostPrice_Net",CCPInventory!$A$1:$Z$1,0))
The problem with this is that the O29 and the CCPInventory!B:B are hardcoded which wont work. The formulas I have are:
=INDEX($A$1:$DD$5000,0,MATCH("External ID",$A$1:$Z$1,0)) returns the correct value for O26 but if I copy and paste this formula over the O26 in the first formula I get an #N/A
and
=MATCH("VAR_SKU",CCPInventory!$A$1:$Z$1,0) which returns 2. Firstly this range is on another sheet "CCPInventory" and secondly I have tried using the address function to give me the column letter but this gives me the address of a cell and not the entire column.
This entire formula has no explicit references since all cols are constantly in different places so I have to create the entire statement based on the column headers hence the reason for so many match statements.
Any and all help would be appreaciated.
Thanks
Steve