Replies

Dana,  thank you – I have now found the spot – got to say it’s well hidden. I don’t think I ever ticked or unticked any of these choices until just now.  So I wonder why some of my spreadsheets had this tick whereas the majority was unticked. Is this always file specific or is there an overall or default setting somewhere too?

Hi Dana,

thank you – based on your explanation I think my mid-work clicking the save button on longer work tasks will be sufficient for me. I still don’t understand why MS creates a backup at the time I close a file when I would say yes to save at that time anyway. If I have a good saved file at the end of my work, why would I need a backup file with the same time stamp in the same folder?  My “regular” backups are on different drives.

Also, when opening the General Options/Settings window, my options are a bit different from your description above.  I am ok with excel saving every 10 minutes but assume that is to the existing xlsx file. This auto-recover pointing to a different folder seems to be a different issue.  Under the general tab I don’t see any option to save or not to save.  File Save seems to do the same as clicking the save icon on the ribbon, so I am still a bit baffled.

thank you again – well done

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.

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.

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

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)

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.

Paul,  I am sure it’s my mistake but using paste either directly or via notepad I am getting the formula in the cell but no action. I was expecting some “n/a”  or a date

Paul, thank you but when typing it in I am getting error messages regarding a missing apostrophe. Using copy/paste I only get a straight paste of the text but nothing happens.

Also what would be the UDF to delete the data?

thank you Joe, looking at this I am already using the 64-bit version – what concerns me is that it seems the older versions of Office/Excel were less likely to choke.  I still have some pc running 2007 and they seem not to mind the formatting. At the moment I am working with 2021 on mine (walking on egg shells) and running planmaker as a backup. Planmaker seems fine with resources but there are some settings I can’t find quickly being used to excel and some functions they apparently do not yet provide (like sorting or filtering by colour).

 

thank you very much

Excellent – I had to use edit comment as an extra step to duplicate this but was then able to replicate this.  Also, I would not have thought to use Edge to look at the sheet first – two more things to file away in my knowledge box.  Thank you.

For my future usage though I will use your data validation suggestion.

Zeddy,

not sure why my comments are causing these issues but even unfreezing does not allow me to see all of them. I am not bothered now as you provided a very good alternative and I am sure there are heaps of other features you could teach me though many of them probably would not find much usage based on my needs.

The attached sample file shows part of the top of the spreadsheet and you can see that the comments for cells B1, D1 and H1 are working fine but C1 and F1 only generate a downward bar with no easy way for me to show the content.  I am sure it’s there but somehow hidden possibly happening when I changed the width of the columsn? I played around with it but still cannot figure it out

1 user thanked author for this post.

zeddy

Zeddy,

thanks for that. While I was still not able to see the original comments for all cells, the alternative with Data Validation works very well for me.  Of course you need to know that some comment may be hidden there but since these comments are mainly for me and always in the same cells, I won’t miss the little arrow. In fact thinking about it I could put a “useless” comment with red marker in there, never intending to open this just to provide a visual clue for the data validation comment.