Extend data range formulas keeping one static cell referenced?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a ticket for entering sales. Calculations are performed wihtin the
ticket. he rows are repeated for each sale. One calculation is set up to
multiply by a static row at the top with data. The formula needs extend with
each ticket yet the reference to the row at the top should not extend because
it never repeats within the ticket. I cannot include the data row within the
ticket because there is a dependent calculation that will be disrupted. Is
there a way to allow a portion of the formula to extend and a portion to not
add rows?
Helpful ideas appreciated. I would rather not restructure the sheet because
it is fit for purpose. It just does not paste and calculate without
editing. The ticket needs to repeat many times and editing would be
difficult.
 
Melissa said:
I have a ticket for entering sales. Calculations are performed
wihtin the ticket. he rows are repeated for each sale. One
calculation is set up to multiply by a static row at the top with
data. The formula needs extend with each ticket yet the reference to
the row at the top should not extend because it never repeats within
the ticket. I cannot include the data row within the ticket because
there is a dependent calculation that will be disrupted. Is there a
way to allow a portion of the formula to extend and a portion to not
add rows?
Helpful ideas appreciated. I would rather not restructure the sheet
because it is fit for purpose. It just does not paste and calculate
without editing. The ticket needs to repeat many times and editing
would be difficult.
Click to expand...


Hi Melissa,

I don't know which formula are you using, so I'll do an example with SUM
function:

=SUM($A$3:A6)

If you input the above formula in A7 and then copy down to A8, A9 and so on,
the formula becomes:

=SUM($A$3:A7) in cell A8,

=SUM($A$3:A8) in cell A9,

and so on...

So you have to use absolute references in the first cell of the range and
relative references for the last cell of the range.


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
Dear Franz,
Brilliant, I appreciate the well explained example. Thank you for the
explanation. I NOW get the concept.
Melissa
 
Back
Top