Absolute Cell References

[Karen]

I have to calculate the minimum and the maximum on the same range of cells
across multiple worksheets. I thought that I would just set up the cell to
calculate the minimum and then just copy and paste the formula in the cell
for the max. and then just change the =MIN to =MAX. When I do this, the
column references change. I'm guessing that the cell references have to be
absolute. Since I have all the minimums calculated, what is an easy way to
calculate the maximum so I don't have to choose each cell across the
worksheets again or change the column references?
Thanks, Karen

 

[David Biddulph]

If you copy from the formula bar, rather than copying the cell, then the
references won't change when you paste in another cell for the MAX.

 

[Bernard Liengme]

If your worksheets all have the same structure (i.e the data is in the same
place and the formulas are to go in the same cells, then you can group the
worksheets and type the formula once.

Click the tab for the first worksheet; hold SHIFT and click the tab of the
last one (note how all the tabs between first and last now have the same
appearance); type formula in the cell and commit it (press ENTER, or use
green check mark in formula bar, etc); click a single worksheet tab to
ungroup.
Now all sheets have same formula.
best wishes

 

[Keith Faulconer]

Karen:

Not sure how your spreadsheet is set up. Are there multiple formulas with
the MIN and MAX that you need to change to absolute reference? If it is set
up in rows like

=min(a4,a54)
=min(a55,a70)
etc....

Then the easiest thing to do would be to go to the edit menu, then go to
replace. In the "Find" dialogue box, enter a, and in the replace with box,
enter $a$

You can then copy the cells over 1 column and the cells will remain the
same.