How to Correct Formula in Multiple Sheets

Discuss the spreadsheet application
Post Reply
MCOOuser
Posts: 3
Joined: Sun Dec 09, 2018 11:27 pm

How to Correct Formula in Multiple Sheets

Post by 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.
Attachments
CalcSceen1.odt
Find & Replace screen Shot
(30.68 KiB) Downloaded 89 times
OpenOffice 4.1.5
Windows 10 Home Version 1803 OS Build 17134.407
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to Correct Formula in Multiple Sheets

Post by Villeroy »

Move (not copy) B6 to B7. All references will follow
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: How to Correct Formula in Multiple Sheets

Post by 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.)
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
MCOOuser
Posts: 3
Joined: Sun Dec 09, 2018 11:27 pm

Re: How to Correct Formula in Multiple Sheets

Post by 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.
OpenOffice 4.1.5
Windows 10 Home Version 1803 OS Build 17134.407
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: How to Correct Formula in Multiple Sheets

Post by 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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
MCOOuser
Posts: 3
Joined: Sun Dec 09, 2018 11:27 pm

Re: How to Correct Formula in Multiple Sheets

Post by 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?
OpenOffice 4.1.5
Windows 10 Home Version 1803 OS Build 17134.407
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to Correct Formula in Multiple Sheets

Post by Villeroy »

Do it sheet by sheet.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: How to Correct Formula in Multiple Sheets

Post by 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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: How to Correct Formula in Multiple Sheets

Post by 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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply