I have a worksheet with columns to be printed and others that adjust those columns, moving across the columns as each period of time expires. Instead of having to change the formula in the printed column, has anyone any ideas on how to have it automatically work all the time? I had thought that if I input a value each period into another cell, it could be taken up by each formula. But I can’t see how to make that work, with different line numbers.
say col d is last period and col e is this period. And the formula in col s is round(d) last period. Can I do something that in effect takes the value of (d) from one cell, so I don’t have to edit a whole column of formulas? (col s isn’t quite that simple, as the contents also include some other formulas, taking values from other sheets. But that part works fine. It’s just the value of that column to the left that is a nuisance.) I can’t change the value in col d, as cols b to col m are summed and that figure is used in col t.
Hope I’ve explained this clearly enough. I’m producing a financial report, each month. Source is several worksheets but then there need to be some adjustments, as there are more lines on the report than there are columns in the source worksheets.
Usually these columns are divided into two sheets - the cells that adjust those columns are on one sheet, and the formatted results, ready for printing, are placed on another sheet.This avoids many of the hassles of setting up printing.
For formulas in different rows to use the value from one specific cell, use the absolute address of the cell or a named cell.
Hope I’ve explained this clearly enough
John, I can’t work out how to nest the formulas to use a cell’s contents for the name of the column. If the formula is, for example, =+ROUND($YTD.AB15+ROUND(E23,0),0), my question is how to replace E23 with a link to another cell with contents to replace the E portion, only. Ignoring relative or absolute, for the moment, just using one occurrence, do you know if it’s possible?
This is not a solution to the problem; please repost it as a comment and delete the (non)answer.
Yes, it’s possible.
So that the formula you gave =+ROUND($YTD.AB15+ROUND(E23,0),0) correctly changes when you shift one column to the right and the E part changes to D, this can be written for example:
=ROUND(YTD.$AB$15+ROUND(E$23;0);0)
No, we’re not moving a column to the right - all the columns exist already. So absolute/relative is irrelevant.
@HVMacca We can continue to communicate in this style indefinitely … well, almost indefinitely - sooner or later one of us will get tired of it. Instead of describing your spreadsheet and a hypothetical one-formula example, just show your spreadsheet. And then, perhaps, you will receive back a spreadsheet with corrected formulas, and not a verbal presentation of the theoretical principles for using Calc.