I have an excel tab that has monthly numbers for a number of different metrics with ROW1 listing the metric that the data is for. COL A is the year and COL B is the month. On a different tab I have set up to do an HLOOKUP by the metric from a pulldown menu with the rows representing months and the columns representing years (each year is it's own column) I currently have each cell do an HLOOKUP using a formula like this =HLOOKUP(metric,sheet,115,FALSE) and each cell has the row corresponding to that month and year. The issue I am running into is that when there is no value it returns $0.00 which skews the conditional formatting of a color scale of highest to lowest values. I have been replacing the HLOOKUP with a formula like this one which places a -- in the cell instead of a zero value =IF((HLOOKUP(metric,sheet,38,FALSE))=0,"--",HLOOKUP(metric,sheet,38,FALSE)). The issue is that when I copy/paste it keeps the row number from the source even when dragging down and I have needed to change it manually.

1) Is there an overall better way to do this?2) Is there a way to paste (or replace) with it keeping the row number of the hlookup from the formula currently in the cell or to have it adjust the row when dragging or copy/pasting?