SOLVED

New Contributor

Changing a formula across disparate cells

So, I have a number of diagrams to create. These are traffic flow diagrams, which show the traffic flows on say 40 junctions in a network, with different direction arrows etc. A big spreadsheet then.

We start with a base diagram with the base traffic flows in the cells. We then copy that sheet and need to change the formula for the cells with traffic flows in to make a change to those traffic flows such as to use a mulitplier to growth these up:

e.g. ='AM Veh 22 D'!CI67'*1.1     the multiplier 1.1 could be another sheet or worksheet reference too.

This formula needs to be applied to all the traffic flows in the diagram. BUT the cells that have the formula in are spread out across the spreadsheet and there are as more blank cells than those with numbers in.

How can I make this quicker than copying the same formula into each and every seperate cell I need to in the diagram. Is there already a way of doing this? I have upwards of 40 spreadsheets to populate in this way so they all recalculate together and are linked, but the continual copy-paste is not good for my patience!

Emma

3 Replies
best response confirmed by Emmag2220 (New Contributor)
Solution

Re: Changing a formula across disparate cells

Find and Replace might be an option to update the formulas quickly. If you're able to share a sample workbook, myself or someone else might be able to recommend a specific solution.

Re: Changing a formula across disparate cells

@Patrick2788  thanks for the quick response. This is a very small snapshot of the type of spreadsheet I have. If you imagine this multiplied by 8 we are there with the amount of cells I need to change. Each cell where there is a number has a similar formula, referencing another worksheet for example. I need to change the formula slightly, but for each cell in my diagram. I want to it a little more quickly than update one cell and the copy-paste to another. I suspect it might have to be some form of code, which I can't do!

Re: Changing a formula across disparate cells

Code may very well do it but the amount of time needed to script the code may be about the same if you were to update the cells manually.