INDEX function need to have col reference to be formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pierre - 31 Jul 2006 21:11 GMT

This works quite nicely:
=INDEX('Price List'!$E:$J,MATCH($E20,'Price
List'!$B:$B,0)+ROW()-ROW(),6)

But

the last cell reference of a value in column "6" in the above formula
needs to be a calculated cell within this formula.
In this case: F3*(1+I3)

Reason being: It seems that the value in column 6 in the range is not
present when the ODBC completes its job from which the data is
extracted. I'm hoping that this calculation can occur in the cell
where the above formula is located.

So to summarize, can I replace the "6" in the above INDEX function with
the result of calculating F3*(1+I3) ?

TIA for any ideas.

Pierre

Reply to this Message

Biff - 31 Jul 2006 21:26 GMT

Hi!

The short answer to your question is yes. Did you try it?

Don't know why you're using this:

+ROW()-ROW()

It's superfluous.

Biff

> This works quite nicely:
> =INDEX('Price List'!$E:$J,MATCH($E20,'Price
[quoted text clipped - 17 lines]
>
> Pierre

Reply to this Message

Pierre - 31 Jul 2006 21:54 GMT

> Hi!
>
[quoted text clipped - 7 lines]
>
> Biff

Biff, thanks for the sanity check. ;-)

The formula works well as =INDEX('Price List'!$E:$J,MATCH($E20,'Price
List'!$B:$B,0),6)
The next row is
=INDEX('Price List'!$E:$J,MATCH($E20,'Price List'!$B:$B,0)+1,6)
=INDEX('Price List'!$E:$J,MATCH($E20,'Price List'!$B:$B,0)+2,6)
etc.
however, we still need to multiply the value in column F by the value
in coulmn I at the row located by the INDEX/MATCH. This would appear
where a column 6 would be if it existed. Attempts at nomenclature have
fizzled in this heat.

Thanks for your interest.

Pierre

Reply to this Message