Timeline for Displaying values in a spreadsheet up to X days away from current date
Current License: CC BY-SA 4.0
14 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 20, 2019 at 19:15 | vote | accept | hiigaran | ||
| Aug 20, 2019 at 18:54 | comment | added | fixer1234 | Kudos for figuring it out, and it's always satisfying to have a part in solving a problem. :-) I tried to include some explanation in the answer for novice Excel users who might have a similar problem. | |
| Aug 20, 2019 at 18:50 | answer | added | fixer1234 | timeline score: 1 | |
| Aug 20, 2019 at 15:48 | comment | added | hiigaran | Figured it out. If you want to post the test condition as an answer, I'll go ahead and accept it. | |
| Aug 19, 2019 at 3:16 | comment | added | hiigaran | A1 contains a formula that sums up how many entries there are. This allows every other formula I have to only go through the exact number of entries I have, so I don't assign an arbitrary range like 1:5000, which would slow down the processing considerably. As for LO vs Excel, I've always used OO/LO, so Excel would likely be confusing by this point for me. | |
| Aug 18, 2019 at 21:31 | comment | added | fixer1234 | BTW, LO Calc can be set to use Excel-style addressing (which includes, among other things, sheetname!range rather than sheetname.range. That will make it easier to get support on SU (unless you have always used LO and that would confuse you at this point, or it would mess up support for a lot of legacy spreadsheets). The site has a lot of Excel pros but far fewer LO pros, and the native LO addressing confuses the hell out of Excel pros who aren't familiar with it. :-) | |
| Aug 18, 2019 at 21:28 | comment | added | fixer1234 | This is going to be hard for people to figure out without knowing what's in the referenced cells. A couple of things to double-check: make sure you don't have circular references. It looks like your data starts in row 3 and you have a reference to A6. It isn't clear what's going on with the $A$1+2 that becomes part of a cell reference in INDIRECT (what's in A1?). I would try testing SUMPRODUCT with each argument separately to see what it's doing. | |
| Aug 18, 2019 at 21:03 | comment | added | hiigaran | Edited my question with one of the formulas I tried using that condition. Getting a 502 error. | |
| Aug 18, 2019 at 21:02 | history | edited | hiigaran | CC BY-SA 4.0 |
added 409 characters in body
|
| Aug 18, 2019 at 20:45 | comment | added | fixer1234 |
You'd basically do it the same way as your name condition. It looks like col L contains the values you're summing? If so, multiply that by this condition, like (L1:L100)*(A1:A100>TODAY()-91). The date test will evaluate to 1 for qualifying dates, or 0 for non-qualifying, so only values associated with qualifying dates will return a value. Actually, you could just make it another argument in the SUMTOTAL: ...(L1:L100),(A1:A100>TODAY()-91)
|
|
| Aug 18, 2019 at 20:38 | comment | added | hiigaran | Summary.A1 is the string to be searched in DataEntry.B1:100. For every row where the strings match, the values in DataEntry.L1:100 are added up. And no, all dates are in the past. As for the condition you posted, could you explain how it works, and how I'd integrate it with the formula in my question? | |
| Aug 18, 2019 at 20:23 | comment | added | fixer1234 | Without trying to unravel your formulas, if col A contains the dates, include a condition `(A1:A100>TODAY()-91). That assumes there can't be future dates in the data. BTW, if you will be copying the formula to populate other cells, you probably want absolute addressing in the range references. | |
| Aug 18, 2019 at 20:15 | comment | added | fixer1234 | Can your data ever contain a future date? Also, col B appears to be text, so it isn't clear what's going on in your SUMPRODUCT. And what's in A1? | |
| Aug 18, 2019 at 19:57 | history | asked | hiigaran | CC BY-SA 4.0 |