Can I use the INDEX function multiple times in the same column on seperate rows?

Frank Nelson

Hello, I am trying to make a calculations sheet that will build a estimate based on input from another sheet. I have a helper reference row that actually grabs the input of a form on the origin sheet, and am using INDEX to reference the information from that row, and then multiply it by input on the same sheet. This worked perfectly fine when all the calculations were done on the same row, but whenever I tried to expand to the next column for ease of reading from the person looking at it, the index function returned a circular reference error. I think I have a workaround, but I figured I would ask here if anyone could help as the workaround would be mildly visually displeasing.
Below is the formulas I used and an image reference.

=VALUE(INDEX([1]:[1], 2) * (INDEX(Values:Values, 3))) —- This formatting worked for Items A>C, but caused an error on D

=VALUE(INDEX([4]:[4], 2) * (INDEX(Values:Values, 6))) —- Item D's

My confusion is that the total formula didn't seem to cause a problem despite also being on a different column.

=VALUE(INDEX([2]:[2], 6) + (INDEX([4]:[4], 6) + (INDEX([6]:[6], 6)))) —- Formula for Total

SSFeatures

Hi @Frank Nelson, INDEX is really good when you want to use it inside of another formula, for example when you're trying to search for a specific row by using INDEX+MATCH.

Since you know exactly which cells your data is on, you can reference the cell directly without INDEX.

For example, this formula would reference the 5th cell in "Column1" column.

=[Column1]5

So you can update your formulas to something like this:

Before:

=VALUE(INDEX([1]:[1], 2) * (INDEX(Values:Values, 3)))

After:
=[1]2 * [Values]3

You can do this for your other formulas as well.

I hope this helps!