Cell References - Absolute

  • EXAMPLE attached


    I've run into an interesting problem concerning an absolute cell reference that I'm stuck on and not sure if there is even a work around for it.


    H11 is a Data Validation List


    Beginning with L11, an nested IF along with an INDEX statement are used to apply a Target Book to Look to the calculations that are used in Columns L and M based on the data in H11.


    The problem is that starting with L20, in this attached example, It uses the value in H11 and NOT H20. I understand this is because of the absolute cell value, but I need the reference to Region (H11, H20, H29) to copy down properly when I copy/paste down the sheet.


    Hope this makes sense and thanks in advance for the help. Any combination of cell reference formulas I can use to make this easier when I copy down the sheet?

  • Re: Cell References - Absolute



    Every formula is different, so you have to enter them all anyway.


    Here is the best I can do

  • Re: Cell References - Absolute


    Thanks for the reply. I should have been more clear. When the Region changes, the Target BTL (I typo'd it as Achieved) will change to match the selected reach.


    Since the BTL target changes, the calculation in colum M will be different for each of the 3 regions. Column L calculations use the value from the INDEX along with column M. The reference to the Target BTL's Region (which is a absolute value) changes every 9 rows. I need to be able to reference every 9th row for each block of calculations.


    For example,


    L11:L17 has to reference H12 in it's calculations
    L20:L26 has to reference H20 in it's calculations
    L29:L25 has to reference H29 in it's calculations


    Ignore the Target BTL cells below the Region, they are there as an illustration and not used to calculate.


    CORRECTED attachment.

  • Re: Cell References - Absolute


    Been messing around with OFFSET to see if I this might work in this particular case. Not having much luck -- am I way off base here trying to use OFFSET?

  • Re: Cell References - Absolute


    Hi EgoPrower,
    PMFJI
    At some point you reference to H$11 (H11) and later H$12 (H12)

    Quote

    L11:L17 has to reference H12 in it's calculations
    L20:L26 has to reference H20 in it's calculations
    L29:L25 has to reference H29 in it's calculations

    Quote

    The problem is that starting with L20, in this attached example, It uses the value in H11 and NOT H20. I understand this is because of the absolute cell value, but I need the reference to Region (H11, H20, H29) to copy down properly when I copy/paste down the sheet.

    If you only want to replace H$11 in block two with H$20 and in block three with H$29, I think the only way of adjusting it is to change that reference in the formulas by highlighting the respective block I.e. L20:L26 and 'CTRL-H' (Edit| Replace) and exchange accordingly.
    HTH
    Stefan

  • Re: Cell References - Absolute


    Quote from StefanG

    At some point you reference to H$11 (H11) and later H$12 (H12)[quote]


    It should be H11.


    [quote]If you only want to replace H$11 in block two with H$20 and in block three with H$29, I think the only way of adjusting it is to change that reference in the formulas by highlighting the respective block I.e. L20:L26 and 'CTRL-H' (Edit| Replace) and exchange accordingly.


    I know that's an option, but I'm looking for something that might be a formula related solution. Thanks for the feedback.

  • Re: Cell References - Absolute


    Hi EgoProwler,


    Try the following formula in L11, copied down. You will have to add the individual +/- percentages to the first block of formulas:


    =INDEX($C$8:$E$8,1,MATCH(INDIRECT("H" & ROW()-MOD(ROW(),9)+2),$C$2:$E$2,0))


    Hope this helps.


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!