How to Correct Formula in Multiple Sheets

[MCOOuser]

Hi,

I have over 100 sheets in an Adjusted Cost Spreadsheet with the following ACB formula in cell K6 but with the starting cell to test for "SPLIT" set to B7 instead of B6:

=IF(B6="BUY";K5+(C6*D6)*E6+(F6*E6);IF(B6="SELL";IF(I6=0;0;K5*(I5-C6)/I5);IF(B6="TXFR OUT";K5+(C6*D6)*E6+(F6*E6);IF(B6="TXFR IN";K5*(I5-C6)/I5;IF(B6="REINVEST INT";K5+(C6*D6)*E6+(F6*E6);IF(B7="SPLIT";K5+(C6*D6)*E6+(F6*E6);IF(I6=0;0;K5*(I5-C6)/I5)))))))

Using the Find & Replace set up as seen in CalcScreen1 attachment, I'm able to click on Find and Replace (Find All is greyed out) each of the starting cells, but the formula doesn't automatically increment (adjust) in the subsequent cells in the sheet. I can copy Cell K6 and 'paste special - formula' to all the cells in the column which will correct the formula, but don't want to manually do this for the remaining sheets.

How do I correct the formula in the starting and subsequent cells in all the sheets with the minimum of manual entry?

Thanks in advance.

[Villeroy]

Move (not copy) B6 to B7. All references will follow

[Lupp]

If all the concerned sheets need a correction/change in cell K7 and that filled down then till (say) K111, you don't need F&R at all.
Select all these sheets at the same time. If they are in contiguous order just click the tab of the first one, then press Shift and click the tab of the last one.
The first one of the sheets will still be active (on top in the view). Do your editing, correction, fill operations and everything in the first sheet.
Now click a single tab of the other sheets. Wow!
If the sheets not are contiguous you need to make the multiselection by adding tabs with Ctrl+Click.

(If the suggestion by Villersoy applies to your case - and it will as far as I can see- it's preferrable, of course. You can also do the move for a multiselection of sheets.)

[MCOOuser]

Hi Lupp,

Thanks for the quick reply! It will save me a ton of time but I'll have to wait until tomorrow to give it as try.

[Lupp]

Be careful when working with multiple selected sheets. The UI doesn't know your intentions. It can only act on your actions - and if you edit/clear/move any cell in the visible sheet it will afflict all the other corresponding cells. This may cause damage due to inadvertent interaction.

[MCOOuser]

Hi Lupp,

I've tried your solution and discovered some issues:

1) Can't just highlight/make active 1st tab, press Shift then click on last tab, and making changes to active tab. When clicking on next tab, only 1st and last tab were updated. After highlighting 1st tab, had to press Shift and click on each tab individually, for changes to apply to all sheets (i.e. like non-contiguous sheets using Ctrl-Shift)

2) All sheets do not have the same number of rows. First sheet has 42 rows and subsequent sheets with more rows do not get changed beyond row 42 and those with fewer than 42 rows have data added. Is there a way to apply the changes up to the last row with data in each sheet?

[Villeroy]

Do it sheet by sheet.

[Lupp]

Mouseclick+Shift generally expands selections while Mouseclick+Ctrl adds another part. This is UI standard, at least on Win systems far beyond a specific software's surface, and also is the case when selecting multiple sheets in Calc. It's the same eg in my banking software and-and-and and in the editor I just type this in (in a Firefox "tab")...

I described the proceeding as it is standard and how it actually works for me in LibO and in AOO as well. That wasn't just "abstract". If there can be settings for your OS or your keyboard configuration or specifics in your user profile that change the interpretation of modifying keys I don't know and I cannot test since I don't sit at your keyboard. You may try if your Ctrl key acts like I described it for the Shift key. ...

No further idea here.

[Lupp]

Another advice coming to my mind: Set a tab colour clearly distinguished from white for all your sheets from the beginning. The currently selected sheets will then be flashy by the large part of the tab area being white.