Forum Discussion
Row dependent function
HansVogelaar Progress of sorts. Instead of subtracting E values from G values, the resulting G values have the E values added to them. For example, G7=G6+E7, and not G7=G6-E7. Going down the G column results in increasing G values, whereas G needs to decrease in value. Over to you again.
Sorry about that. Change the formula to
=INDIRECT(ADDRESS(ROW()-1,COLUMN()))-INDIRECT(ADDRESS(ROW(),COLUMN()-2))HansVogelaar Thank you again for the info. Useful to know for future reference.
The formula itself is specific for your request.
Ctrl+Enter to enter a formula in all cells of the selected range is a standard Excel shortcut. You can find it in (for example) Keyboard shortcuts in Excel
By the way, this would also have worked:
- Select G7:G100
- Enter the formula =G6-E7
- Press Ctrl+Enter
HansVogelaar Got it, instantaneous and simultaneous. Thank you for your assistance. As an aside, is this formula and procedure to be found somewhere else within Excel as a standard formula and procedure? Or did you produce it in order to answer my enquiry? If necessary, I would be interested to learn how to produce other non-standard formulae and procedures any time in the future.
Hold down the Ctrl key when you click on the ✓ icon or press Enter.
HansVogelaar Minus instead of Plus, of course, I should have spotted that.
This is what happens at present. G values now decrease going down the G column.
But, I select a series of cells in the G column, say G7 to G20. I paste the formula into the formula bar. I click the tick symbol. The correct value appears in G7, but the remaining G8 to G20 cells are deselected, and do not show any decreasing values. If I select each cell individually, and click the formula in the formula bar, then each cell shows the correct value. But this is time consuming. I am looking to get the G values to appear simultaneously from a single click of the formula.