Problems with Indirect and Deleting/Adding Columns!

andy

I am having a problem with the indirect function whenever I have to add or remove columns. I will paste the problem part of my formula below.

If I add or delete a column, then it still references AW in the indirect part and then it changes to the correct column for the other part of the sumproduct. I really need it to be adaptable. (AV6 of course has the row I need to draw from)

Any ideas how to do this??
Thank you for your help!
Code:SUMPRODUCT(INDIRECT("$AW"amp;$AV$6):INDIRECT("$BP"amp;$AV$6),
$AW11BP11),Hi,

I believe one of the main highlights of INDIRECT is that it doesn't re-adjust. The only way I can think of of achieving this is using VBA and I can't think how this would be achieved simply.

untested but couldn't you replace the INDIRECT with an INDEX approach ?

=SUMPRODUCT(INDEX($AW$1BP$1000,$AV$6,0),$AW$11BP$11)

I think Luke's approach with Index() solves your problem. I'd just remove the row limits:
=SUMPRODUCT(INDEX($AWBP,$AV$6,0),$AW11BP11)

Thanks PGC -- I put the limits in to keep range small(ish) but failed to add a note as to why... would it not be a "bad thing" to reference the entire columns memory-wise (even if you only then reference 1 row within that range) ?

I don't think so, Luke. As you say it's just used to reference the column, it's not used to allocate anything and it's not processing any calculations, it's just pointing to the column.
If, by design, I had defined the first row of the table as 1, and the maximum row 1000, in that case I'd use the row limits. It would help me detect possible errors in the AV6 value. In this case I would probably use a named range for the table.
Cheers

Thanks guys,
I really have to learn more about that Index function!

PGC, thanks for the clarification... I'm rarely 100% sure about anything so try where possible to avoid advocating one approach over another unless I know the facts... so the more you guys tell me the better

On an aside, if you're bored and/or have the inclination do you fancy a crack at this ?
forum/showthread.php?t=347872

no one has yet to take up my challenge and I believe it requires someone with  a certain level of expertise... (ie far above my own)

Cheers
Luke