•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Formulas Across Worksheets that are Dynamic and can Accomodate new sheets/conditions
Our take
Managing data across multiple worksheets can be streamlined by utilizing dynamic formulas that adapt as you add or remove sheets. By leveraging a structure that sums values between designated sheets, you can easily capture all relevant data. However, when conditions vary across those sheets, it becomes essential to incorporate criteria that dictate whether to include or exclude specific cells in your calculations.
Not sure if title makes sense but here is a problem I'd like to solve (as simply as possible). Lets just say I want to sum the same cell on a number of worksheets but I want to be able to add worksheets. That can be accomplished by putting sheets between two sheets called First:LastCell... at least that's what google told me and it worked. So if I add sheets between First and Last it will get captured in the formula. So what if I want to do the same thing but based on a condition in each sheet? As in I have a cell reference call out to either exclude or include for each sheet in the formula? Apologies if this is confusing.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Sum of a range based on 2 different criteria?Hello, I'm working on a document which is a holiday funds to keep track of individual person's remaining balance to pay. I want to use a formula to automatically calculate the sum of a range of cells which lie against a certain year under a person's name. Below as an example: https://preview.redd.it/1p19n1o8qrxg1.png?width=991&format=png&auto=webp&s=2e67c0337da92ae1feffe43fc2c7fbaba237d672 As above, I want to look up Name 1 in 1:1, look up Year 1 in A:A and then return the sum of those numbers in that Year 1 range to give me my final result. Is this possible? I've tried all sorts of SUM, SUMIF, SUMIFS, XLOOKUP and combinations but can't seem to get it to work. I also don't want to have to manually select the cells for year SUM as this is constantly updating and want it to be automated. Thank you! submitted by /u/celestialspace [link] [comments]
- Formula for adding/subtracting on a conditional value.Hi, I'm setting up a spreadsheet for my partner and I as we are consolidating our savings but want to keep track on who has what amount of money. (We are consolidating for interest reasons, not for joint finanical reasons) I have created the spreadsheet below, and basically what I want is: sum all [person b + input] subtract all [person b + output] from [sum input] to give final value of money ^^ and again for Person A I'm not good with creating my own excel functions at all, so I'm really struggling https://preview.redd.it/h858fn3cgfwg1.png?width=717&format=png&auto=webp&s=35036974049799ebec79d5daf9affc08ae8a60d9 Sorry if this doesn't make sense! submitted by /u/mudkip0099 [link] [comments]
- Building a dynamic formulaI'm using Excel to predict the capacity usage of storage devices. It's clunky, but it works great until someone changes the name of the SharePoint folder I'm using to reference my data. (This has happened twice) I had the brilliant idea (or so I thought) to dynamically build the formula using "concat", which I thought would allow me to simply change one cell and be able to affect all of my entries (about 65 rows). I then learned that "concat" only builds the formula, it does not evaluate it and that I needed to use "indirect" to evaluate it. That worked, except my output was "REF", not what I was expecting. After a little more digging, I found that "indirect" does not work with external workbooks that are not open on the local machine. (I don't want to open 60 workbooks to get the data I need) It looks like I am out of luck, but I am hoping someone else has a brilliant idea I have not considered yet. Thanks submitted by /u/Separate-Tomorrow564 [link] [comments]
- How to sum a conditional formula across multiple cells?Hi, I'm looking for an elegant solution to this problem. Mostly just a curiosity because I currently do have it working, but I know there is a cleaner way to do this. Basically I am trying to sum the number values from a row of 5 cells, where the cell may or may not start with a letter (i.e., the row could be "8, 9, S6.5, 8, A6" and I want the result of 8+9+6.5+8+6=37.5). I have many rows of this, and the summed value ends up in the column to the right of the 5 values. I've managed to do this with the following formula, where the result is in cell J5: =IF(ISTEXT(E5), RIGHT(E5, LEN(E5)-1), E5) + IF(ISTEXT(F5), RIGHT(F5, LEN(F5)-1), F5) + IF(ISTEXT(G5), RIGHT(G5, LEN(G5)-1), G5) + IF(ISTEXT(H5), RIGHT(H5, LEN(H5)-1), H5) + IF(ISTEXT(I5), RIGHT(I5, LEN(I5)-1), I5) But, if I recall from years ago, there is a way to make the formula act on an array of cells using ctrl+shift+enter so the formula would essentially look like this, and do the same thing: ={SUM(IF(ISTEXT(E5:I5), RIGHT(E5:I5, LEN(E5:I5)-1), E5:I5))} Thing is, it doesn't seem to cooperate, and I have gotten pretty rusty with excel. This array formula will sum values but not if there is text in the cell. Example data: M T W Th F SUM T8 T8 9 9 5 39 9 8 8 A8 8 41 8 8 8 8 8 40 S8 9.5 8 8 6 39.5 submitted by /u/ganduvo [link] [comments]
Tagged with
#google sheets#AI formula generation techniques#Excel alternatives for data analysis#formula generator#rows.com#natural language processing for spreadsheets#generative AI for data analysis#Excel compatibility#cloud-based spreadsheet applications#Excel alternatives#formulas#worksheets#dynamic#summing#cell reference#conditions#captured formula#include#exclude#First:LastCell