-1

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?

2
  • You provided a good explanation of the setup but it isn't clear what the specific issue is. Is it just getting names to work (last paragraph), or is there more to the problem?
    – fixer1234
    Oct 28, 2014 at 21:10
  • The issue is described in the last paragraph. I'll edit it to clarify.
    – tzvi
    Oct 28, 2014 at 21:59

1 Answer 1

0

I discovered that INDEX(PPRICE, 2) will return the value of the second row in the PPRICE column. The entire code snippet will look like this:

=IF(INDEX(PPRICE, 2)="",
INDEX(DPRICE, MATCH(Products!A2, DCAT, 0)),
INDEX(PPRICE, 2))

However it will be an absolute value, not a relative value and if you copy paste this formula to other rows it will still point to row 2. This would therefore not be a good idea for large data sets.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .