INDIRECT OFFSET Referenced column

The word "external" is usually used to talk about data in another file. You are talking about references between worksheets of the same file.

I think you are confused about what INDIRECT does for you. INDIRECT takes a single argument that is a string containing a cell reference. For example

=INDIRECT("A1")

is the same thing as saying

=A1

It can get much more complicated but I'm not going to go into that because INDIRECT is the wrong function for you to use here. Your formula is

=INDIRECT($C$11)

That means "In C11, there is a string that looks like a cell reference. Use that string and return the referenced cell." But C11 contains the string "Rate1" which is not a valid cell reference.

Now we come to what you actually need. In C11 you have selected a rate card, Rate1 in this case. In C16, you need to find the rate in the corresponding row on sheet "Rate Card" which is in the column with the heading Rate1. Use this formula in C16 and copy as necessary to other cells in column C.

Formula:

Please Login or Register  to view this content.