Forum Discussion
How Do I Calculate Different Cells Using Different Formulas?
Try this:
=(N5+Q5*(Q5<0))*C5+B6The part (Q5<0) return TRUE (or 1) is Q5 is negative, FALSE (or 0) if positive. Hence, in the first instance you will multiply Q5 by 1, in the other instance Q5 by zero.
Nickerz_2020 The comment regarding a new thread was a tip for the future. Anyway, in the Trust Center (File, Options) you can change the settings. You'll see that "Enable automatic update of links is NOT RECOMMENDED.
Got it, thanks for the tip Riny and the quick reply!
I guess I'm confused as to why this suddenly started happening. ? I don't share this spreadsheet and I've only been updating the formulas from these forums, which is why I thought this has something to do with the recent formulas I copied and pasted.
I opened my Trust Center and I have all the default options that you show in your screenshot above but to try to get rid of the pop up window (which now happens each time I open my spreadsheet, no matter which option I choose, update/don't update) I tried selecting "Disable..." for the 3 options above but I'm still being prompted to update/don't update for the latest data. ????
Again, I'm just confused as to what my spreadsheet is trying to do but even more confused as to why I'm now having to deal with this when I haven't changed any of these settings. Just weird...
***UPDATE***
Just now, I clicked on the Data tab>Existing Connections but found none (see attached screenshot below...)
So then I tried Data>Edit Links>Break Link
...and it pops up a warning, I then clicked on Break Links (since I have another copy of this spreadsheet saved) and I'm no longer prompted to Update the links!
My spreadsheet looks fine as do the formulas (as far as I can tell at the moment) but again, I have NO idea how this issue started. ? Just came out of left field all of a sudden. ?????
Yep, that tool found it and I was able to correct it! Thanks again Riny!!!
Turns out that my spreadsheet had a tab (Billing Wk #06 - this was the invoice week I found that was positive so the formula above needed to be revised to round up) and when I was copying and pasting formulas, I accidentally pasted the wrong info into a different cell with a formula (note to self...see your screenshots for today for details in case you have to reference this fix in the future). So I feel much better now knowing exactly why this started happening and I was able to correct it thanks to that very useful and easy to use findlink tool!
I had to add the findlink as an add-in even though it says it can be ran once. I did this using the steps they had in that link...
- To install as an add-in in Excel 2010 or later
- File > Options > Add-Ins > Manage: Excel Add-Ins > Go > Browse to where you saved FindLink.xlam, Select FindLink.xlam and click OK
Below are some screenshots with more detail...
findlink Add-in Tool (added to the Ribbon in 'Data' Tab):
Found Formula Link Screenshot:Confirmed - No Excel Formula Links (after correcting the formulas myself instead of having findlinks delete them - I just wanted to be sure and glad I did it myself as the cells still had formulas so not sure if those would have been lost):
No Excel Links Found:
You're the best Riny and you have been VERY helpful this entire time. Again, thank you sir!
Nick
- To install as an add-in in Excel 2010 or later
Nickerz_2020 A link to an external workbook must have been inserted somewhere at some point. You can use the tool in the attached link to find it and decide what to do with it.