January 9, 2020
I am currently creating a excel sheet which will be used to calculate the cost of an item by looking up a referencing code and applying a pre-written rate to it.
I have used the "IF" function to look up the reference code from a list and then I want it to use the rate I have entered in the cell next to it. The issue I am having is that if I click on the cell with the rate formula I get #VALUE!, I believe this is because the rate itself is made up of a calculation.
An example below to make it more clear;
This is my formula,
=IF(H2='Dictionary '!$A$4,I2*'Dictionary '!C4,IF(H2='Dictionary '!$A$5,I2*'Dictionary '!C5,0))
The reference in bold are for rates written in other cells e.g. 10.7639*150*0.1*3
If I manually enter the calculation into the formula it works but I want to be able to edit the one cell with the rate and then my formula will automatically update as there will be hundreds of lines.
Any help is appreciated,