I'm using Excel 2010 and am creating a workbook to define details about products in a store (including category, price, accessories). I also want to define default category details to use if the specific details of a product are left blank.
I set up three sheets: Products, CategoryDefaults, and Calculated. Columns are the product/category details and each item /category has its own row. I would also like to use named columns as much as possible for readability. A column name starting with P is the Product detail and one starting with D is from the CategoryDefault sheet.
The fields in Calculated look similar to this:
=IF(Products!G2="",
INDEX(DPRICE, MATCH(Products!A2, DCAT, 0)),
Products!G2)
where DPRICE is a named column for default pricing and DCAT is the category name for the CategoryDefaults sheet.
The formula means as follows: If Product number 2 Column G is blank, then find the value of Column A Product 2 in the CategoryDefault sheet and return the DefaultPrice. Else return the defined Price.
So far this all works fine. I would like to take it just one step further: In the previous example Products!G2 refers to the price column for product in row 2. I would like to make to formula more readable by replacing the Column letter reference 'G' with a named column reference, namely 'PPRICE'. I would do the same with the reference to Products!A2 by calling the column 'PCAT' (the category the product belongs to)
The final formula would look something like this:
=IF(Products!PPRICE:2="",
INDEX(DPRICE, MATCH(Products!PCAT:2, DCAT, 0)),
Products!PPRICE:2)
I just can't get this to work. I've tried "Apply Names..." with no result and any variation of colons and $ to no avail. Has anyone done this before?