Hi all,
I hope you're well.
I've got a drop down box that shows options from a table in another worksheet. There is then a second drop down box whose options populate from the same table dependent on which option is selected in the first drop down box. I'm then trying to create a formula in a cell that will show a corresponding value from the table based on what options are selected in the two drop down boxes.
I've managed to do this no problem on the worksheet that the table is located (done as an initial test) but I am having trouble when I try to do the formula on a separate worksheet linking to the worksheet with the table on. Worst case I can put the table onto the other worksheet but I would prefer not to so as to limit clutter.
I've used a combined IF AND statement which as I've said works on the worksheet with the table on it. However, when I try to do this on another worksheet, it doesn't seem to like the reference back to the other worksheet. The strange thing is that it doesn't have an issue with it for the two conditions in the formula but does have an issue with the value if true part of the formula. The worksheet with the table on is called Restraint Con. and the formula (shortened as it repeats for many values but the same issue is throughout) is:
=IF(AND(B11='Restraint Con.'!A18,B12='Restraint Con.'!H18),'Restraint Con.'!K18,IF(AND(B11...
With B11 and B12 being the two drop down boxes. When I hit enter, Excel says there is a problem with this formula and highlights the third Con.' in the formula as if it doesn't like that but seems to be fine with the first two when they aren't any different. If I delete that Con.' and then hit enter it gives the same error message and highlights Con.' again but on the next IF AND statement within the formula (again not the B11= or B12= part but the value if true part).
Does anyone know why this is and how to rectify it?
