using find & Replace with changed references

Topic

New Reply

[beethoven]

I updated a spreadsheet and deleted some columns that are no longer required. As a consequence the references used in cells no longer match. How can I use Find/Replace to fix this?

The current formula leading to the #Ref! output is =#Ref!-AE4766  – I need to replace #Ref! with M4766 but have this continue to M4767 and so on with M being the constant and the row number adding as usual?  I can do this manually or use sort and copy larger ranges but if I want to be efficient, I would like to use find & Replace for the whole range. Is it possible to enter the $ sign in Find and Replace ?  I can’t get it to work.

For some of the cells the the second part of the formula is not a reference cell but a number. So the majority of the cells will be = M4767 – AE4767, followed by M4768-AE4768, however in between will be =M4769-150 and =M4770-38

• This topic was modified 2 years, 3 months ago by beethoven.

Reply | Quote

Replies

If you can change the formula so that it automatically uses 150 and 38 if required, then you can change one formula and fill. This is always a better way to use formulas.

How do you decide when to use a number instead of a reference?

cheers, Paul

Reply | Quote

Hi Paul,

Happy New year

the formula is not constant for every cell – all will refer to M2 to M5300 and then I need to deduct an amount.  This amount to deduct can be a amount I am guessing (projection of future sales without any historical data and as such that may vary ) and for most other cells the amount to deduct is based on historical data in a different column, eg. AE2 to AE5300. Using Find/Replace works for me if I refer all cells to M2 (to M5300) minus either a constant number or a reference to a constant cell range but not if the second part varies.

Reply | Quote

Add a column for your guesstimated value and use an “if” to include that in the calculation. Then you will have consistent formulae and a way to tell if you “cheated”. 

cheers, Paul

Reply | Quote

thank you but what would the formula say assuming my conditions below and how to work with find and replace and not get short stopped by : “There is a problem with this formula. Not trying to type a formula? When the first character is an equal (“=”) or minus (“-“) sign, Excel thinks it’s a formula:…

find/replace  #Ref!

with

=M2-(Af2-80) (but only if AF2 has a value, otherwise take the data from AE2)

Reply | Quote

I can’t see a way to do it with F/R, so you need to rewrite one formula to cater for the new column and then fill.

If AF2 is a number, use AF2, else AE2
=IF(ISNUMBER(AF2), M2-(AF2-80), M2-(AE2-80))

cheers, Paul

Reply | Quote

thanks Paul but I still got the outcome wrong – probably did not explain the setup properly.

Example:  Current Stock M2=610   Estimated Sale in new Cell AF2= 50  – then I would like to see as result 560

Example:  Current Stock M2= 194  Estimated Sale not needed as data available in cell AF2 =5 – then I would like to see as result 189

at the moment it seems the formula is ignoring the starting stock in M2

Reply | Quote

It can’t ignore M2, it’s in both formulae.
I can’t really see how to get your requested outcome – maybe a very small sample sheet would be easier, or a screenshot?

cheers, Paul

Reply | Quote

Paul,

you are quite right – easier with a small spreadsheet ( see attached) .

Column M shows the starting qty

Column Z – projected stock after sales – here we need the working formula

most sales will be taken from Column AG – so that’s why the formula in column z is =M2-AG2, however some items did not sell in that reference cell and I need to reference AE2 (highlighted in yellow and some items are new without historical data (highlighted in green)  so I need to deduct a guessed qty , from your suggested AB column)

So, every single calculation need to reference column M but the figure to deduct depends on variables.  My original problem was that every few weeks when I start a new campaign, all references will move ( Columns AH to AO are all older sales data). Once I move to the next campaign, I remove some of the projection columns as they are no longer required and that’s when the references break . I will then do some clean-up and originally  I was hoping to use find/replace and to replace #Ref! with the new cell without changing the subsequent data in the formula. Using the helper column AB  instead of find/replace is workable for me but I need to get your if condition working.

Reply | Quote

You seem to have 3 calculations, based on whether there is data in AB, AE or AG.
This works for all 3: =IF(ISNUMBER(AB2), M2-AB2, IF(ISNUMBER(AG2), M2-AG2, M2-AE2))

What columns are you removing  at campaign end? We may be able to hard code them.

cheers, Paul

1 user thanked author for this post.

beethoven

Reply | Quote

Thanks Paul,  testing this on my sample sheet, it’s perfect. I will give it a spin tomorrow on the real data set.  I think this will serve me well.

There may be slight complications and or I  may have to do some pre-sorting of the rows as the fact that I use column AE instead of AG does not necessarily mean that AG has no data. All columns from AE onwards are sales results. Sometimes the prime reference cell does have some sales but they are not representative and I would rather use a different column (e.g. seasonal stock). I think that’s where I may need to use a two step approach (sorting first, applying your formula to a subset and then sort again and apply amended to a different subset).  All of these projections are only necessary when I don’t have actual data yet of the campaign. Once I have data for Campaign 160, the formulas would be easier again as I could just use in column Z :   =M2-(AC2-AD2)

Your formula is very useful when I do my projections based on prior data.

As for hard coding, not sure how this would be done but in principle each time I move forward the latest sales data are being entered into a new column to the left. At the moment columns AC and AD are hidden/mimized and still empty.  AD are actual Sales and AC projected sales for the life of the campaign. Once the campaign is finished column AC will be deleted and at that time I am encountering the error with #Ref!

Sorry, it’s all a bit convoluted but I really appreciate your patience.

Reply | Quote

You should always try to set up your work flow so that you don’t need to adjust formulae. Then you can’t make a mistake during editing.

When you delete a cell/row/column that is referenced in a formula you get a #ref error. A better method may be to use a helper cell at the top of the sheet that contains the address of the current data column. When you delete old campaigns you only need to adjust a single cell.
e.g. cell A5 contains the text “AG”
Your formula would now be: =IF(ISNUMBER(AB2), M2-AB2, IF(ISNUMBER(INDIRECT(A5&2)), M2-INDIRECT(A5&2), M2-AE2))

INDIRECT retrieves the column reference from A5 and adds the row reference “2” to get the actual value from “AG2”. When you delete column AG, put the next column address in A5 – as text without quotes.

cheers, Paul

Reply | Quote

thank you again – well done

Reply | Quote

Oops! I knew I would miss an absolute reference somewhere.
“A5” is an absolute reference and must not change when you fill the formula. To do this we add dollar signs in front of the row/column/both that must not change. This gives you “A$5”, but to be safe we’ll use “$A$5”.

=IF(ISNUMBER(AB2), M2-AB2, IF(ISNUMBER(INDIRECT($A$5&2)), M2-INDIRECT($A$5&2), M2-AE2))

cheers, Paul

Reply | Quote