# Dumb Excel Questions **URL:** \ **Category:** Software/Technology\ **Created:** [February 3, 2022, 6:03pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148 "2022-02-03T18:03:06Z")\ **Posts on this page:** 20\ **Page:** 5
**Author:** ![SredniVashtar](https://community.goactuary.com/user_avatar/community.goactuary.com/srednivashtar/32/2894_2.png) [@SredniVashtar](https://community.goactuary.com/u/SredniVashtar)\ **Post date:** [November 25, 2022, 3:55pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/82 "2022-11-25T15:55:20Z")
True, I guess it would be easier to just put the media directly in the file. ---
**Author:** ![DeepPurple](https://community.goactuary.com/user_avatar/community.goactuary.com/deeppurple/32/1551_2.png) [@DeepPurple](https://community.goactuary.com/u/DeepPurple)\ **Post date:** [November 28, 2022, 3:09pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/83 "2022-11-28T15:09:28Z")
> [@SredniVashtar](#): > > If you manage to actually slip these things into a spreadsheet, I’ll commend you. There are some ways if you’re clever. (.) (.) Like the two preceding parenthetical sentences that may or may not have contained slander or vulgarity so they were edited out. ---
**Author:** ![Vorian\_Atreides](https://community.goactuary.com/user_avatar/community.goactuary.com/vorian_atreides/32/65623_2.png) [@Vorian\_Atreides](https://community.goactuary.com/u/Vorian_Atreides)\ **Post date:** [November 29, 2022, 2:42pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/84 "2022-11-29T14:42:19Z")
Staying abreast of the latest & greatest Excel has to offer? ---
**Author:** ![1695814](https://community.goactuary.com/user_avatar/community.goactuary.com/1695814/32/38506_2.png) [@1695814](https://community.goactuary.com/u/1695814)\ **Post date:** [November 30, 2022, 6:31pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/85 "2022-11-30T18:31:01Z")
New topic: Multiple Windows of same Workbook I sometimes have multiple windows of the same workbook open ( **Alt** -vie **W** - **N** ew). Let’s say that the name of the workbook is TPSReports.xlsx. There are three sheets, “Input”, “Calculation”, & “Output”. Since I have multiple windows, one of them is named “TPSReports.xlsx - 1” and it is open to the **Calculation** sheet. The other is named “TPSReports.xlsx - 2” and it is open to the **Output** sheet. This is the active window on my desktop. In this window, I want to [F5], “‘Input’!$BA$42”, [enter] so that the “- 2” window goes to the Input sheet. However, the “- 1” window goes there instead (even though it wasn’t the active window) & the “- 2” window just stays as it was. I tried something like [F5], “‘[TPSReports.xlsx - 2]Input’!$BA$42”, [enter], but Excel was like OMGWTFBBQ42! Am I just SOL on this one or is there a way? If so, do your best Peter Frampton imitation & show it to me. ---
**Author:** ![dr\_t\_non-fan](https://community.goactuary.com/letter_avatar_proxy/v4/letter/d/a8b319/32.png) [@dr\_t\_non-fan](https://community.goactuary.com/u/dr_t_non-fan)\ **Post date:** [November 30, 2022, 6:52pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/86 "2022-11-30T18:52:39Z")
God, I hate when something’s happening like this. I hope you feel the way I do. I wanna be by your side, that’s all I wanna be. I mean, it’s a plain shame. I’d give a penny for your thoughts, but the lines on my face invoke a wind of change. I feel it’s now or die. Not trying to shine on, but I’ll give you money, so I can go to the sun. I wanna go there. ---
**Author:** ![Kenny](https://community.goactuary.com/user_avatar/community.goactuary.com/kenny/32/53_2.png) [@Kenny](https://community.goactuary.com/u/Kenny)\ **Post date:** [November 30, 2022, 7:06pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/87 "2022-11-30T19:06:45Z")
I didn’t know this was possible. Interesting. If you make changes in “-2” or “-3” Are those changes always reflected in “-1”? It is possible that “-2” & “-3” are basically view only instances so all “real” changes occur on “-1”? ---
**Author:** ![1695814](https://community.goactuary.com/user_avatar/community.goactuary.com/1695814/32/38506_2.png) [@1695814](https://community.goactuary.com/u/1695814)\ **Post date:** [November 30, 2022, 7:14pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/88 "2022-11-30T19:14:51Z")
> [@Kenny](#): > > If you make changes in “-2” or “-3” Are those changes always reflected in “-1”? Yes. You could, e.g., have both -1 & -2 open to the same sheet. Make a change on one of them and :viola: there it is also on the other. > [@Kenny](#): > > It is possible that “-2” & “-3” are basically view only instances so all “real” changes occur on “-1”? I wouldn’t call them “view only” because you can edit any one of them and they’ll all change…because they are all one of the same thing…but, yeah, the “real” changes occur on the “real” workbook and those changes are (immediately) seen/reflected/integrated in all of the copies floating around. I’m just surprised that when I’m looking/editing -2 or -3 that I can’t F5 my way to another sheet in that version. (I can within the same sheet that I’ve viewing in -2 or -3.) P.S. and FYI, if you save the workbook with multiple versions open then when you open it up they’ll all open up as well…a fun practical joke to play on the accountants. ---
**Author:** ![1695814](https://community.goactuary.com/user_avatar/community.goactuary.com/1695814/32/38506_2.png) [@1695814](https://community.goactuary.com/u/1695814)\ **Post date:** [November 30, 2022, 7:31pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/89 "2022-11-30T19:31:02Z")
> [@DeepPurple](#): > > =sumproduct(a1:a10,–(b1:b10=“ABC”),–(c1:c10=“XYZ”) + --(C1:c10=“123”)) btw, I wish I could ❤ this post numerous times because it has been super helpful to me. Instead, I’ve settled for ❤-ing it (2^n)+1 times where n ∈ Z+. ---
**Author:** ![AlexioXela](https://community.goactuary.com/user_avatar/community.goactuary.com/alexioxela/32/550_2.png) [@AlexioXela](https://community.goactuary.com/u/AlexioXela)\ **Post date:** [December 2, 2022, 6:16pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/90 "2022-12-02T18:16:10Z")
Unfortunately what I have to do when I want to accomplish this is in window 2 manually navigate to the ‘Input’ tab, then [F5] (I usually click the name box instead, but it seems functionally identical, good to know [F5] works too) and then use either ‘Input’!$BA$42 or just $BA$42. Skipping to window 1 only seems to happen if the “go to” is trying to navigate to a different sheet. If there are a lot of tabs, the best way to manually navigate to the sheet is right clicking the arrows to the left of the sheet tabs. ---
**Author:** ![1695814](https://community.goactuary.com/user_avatar/community.goactuary.com/1695814/32/38506_2.png) [@1695814](https://community.goactuary.com/u/1695814)\ **Post date:** [December 2, 2022, 7:40pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/91 "2022-12-02T19:40:09Z")
Thanks. At least this verifies that I’ve got to do what I’m doing. ---
**Author:** ![SteveGrondin](https://community.goactuary.com/user_avatar/community.goactuary.com/stevegrondin/32/3257_2.png) [@SteveGrondin](https://community.goactuary.com/u/SteveGrondin)\ **Post date:** [December 14, 2022, 3:42pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/92 "2022-12-14T15:42:39Z")
So I have a range (or table) that is three columns (Scen,Month,Data) and want to build a range with Data organized with Scen as rows, Month as Column. Any ideas on whether SUMIFS that are written to return a single value are superior (e.g. SUMIFS(Data,Scen,row,Month,column)) to an INDEX(Data,rowformula) where rowfomula is based on number of months in each approach? It seems SUMIFs is easier to code, but does it make a largeish worksheet lag? In the case of equal months for every scenario rowformula is straightforward. I could use MATCH in cases where amount of scenarios/months are unpredictable. Any opinions? ---
**Author:** ![SredniVashtar](https://community.goactuary.com/user_avatar/community.goactuary.com/srednivashtar/32/2894_2.png) [@SredniVashtar](https://community.goactuary.com/u/SredniVashtar)\ **Post date:** [December 14, 2022, 3:56pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/93 "2022-12-14T15:56:56Z")
Yeah, I expect the sumifs is always slower. Your index formula could be faster, or even instantaneous if you know the formula or use some sorting tricks. On the other hand, it probably doesn’t matter. Unless your sumifs depend on volatile formulas (in which case we should talk), the calculations will just happen when you update the data, so who cares. Broadly speaking, it’s considered immoral to optimize before you have problems. "premature optimization is the root of all evil (or at least most of it) in programming.” --old quote. ---
**Author:** ![1695814](https://community.goactuary.com/user_avatar/community.goactuary.com/1695814/32/38506_2.png) [@1695814](https://community.goactuary.com/u/1695814)\ **Post date:** [December 14, 2022, 4:00pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/94 "2022-12-14T16:00:39Z")
I asked [chat.openai.com/chat…it](http://chat.openai.com/chat%E2%80%A6it) preferred sumifs because it’s designed to sum based on multiple criteria, so it can execute more quickly than index/match. Keep in mind, as far as openai goes, accuracy wise, you’d better keep your fingers crossed ---
**Author:** ![DeepPurple](https://community.goactuary.com/user_avatar/community.goactuary.com/deeppurple/32/1551_2.png) [@DeepPurple](https://community.goactuary.com/u/DeepPurple)\ **Post date:** [December 14, 2022, 7:48pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/95 "2022-12-14T19:48:11Z")
> [@SteveGrondin](#): > > Any opinions? 1. The volume of data could affect my response. Are the rows measured in the original data in dozens, hundreds, thousands, or millions? 2. I would likely answer that my preferred strategy would be sumproduct, not sumif, sumifs, index, or index/match. (subject to idea #3 below) I’ve said here before, I personally like sumproduct. Sumproduct is just a natural way to use boolean logic and binary numbers. **As I have said before, there are 10 kinds of people in this world. Those that understand binary numbers and those that don’t.** 3. If the rows of data are many (Say 1000 or more) then even I would use a pivot table because what you are asking is really “How do I make an efficient pivot table without using pivot tables?” The answer might be, just spend a few hours playing with pivot tables to get comfortable making 'em. My friend Olap told me so. ---
**Author:** ![SteveGrondin](https://community.goactuary.com/user_avatar/community.goactuary.com/stevegrondin/32/3257_2.png) [@SteveGrondin](https://community.goactuary.com/u/SteveGrondin)\ **Post date:** [December 14, 2022, 8:32pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/96 "2022-12-14T20:32:15Z")
Some use cases are 50 x 720, so small, the largest are 600K x 50, so bigger. My gut feeling regarding sumproduct/sumifs is why am i performing a bunch of multiplications/additions when I just want one value? Of course I don’t know how INDEX really works, so I might be making a false comparison. Pivot Table would also be a good approach, just need to make sure it gets refreshed. Not sure where it resides in the speed comparsion. As for optimizing before a need to optimize, I just saw my first example of multikey selection using SUMIFS. I have been using a concatenation of columns strategy to make a single key for a long time, and was curious if I should use a new way. ---
**Author:** ![1695814](https://community.goactuary.com/user_avatar/community.goactuary.com/1695814/32/38506_2.png) [@1695814](https://community.goactuary.com/u/1695814)\ **Post date:** [December 14, 2022, 9:18pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/97 "2022-12-14T21:18:38Z")
From an ego standpoint, I would use INDEX/MATCH because it’s more fancy, more difficult for others to decipher, makes me look like I really know my stuff. ---
**Author:** ![dr\_t\_non-fan](https://community.goactuary.com/letter_avatar_proxy/v4/letter/d/a8b319/32.png) [@dr\_t\_non-fan](https://community.goactuary.com/u/dr_t_non-fan)\ **Post date:** [December 14, 2022, 9:26pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/98 "2022-12-14T21:26:43Z")
You’re looking for single instances in data of combinations of restrictions? Just get the data that way in the first place, before downloading to Excel. ---
**Author:** ![DeepPurple](https://community.goactuary.com/user_avatar/community.goactuary.com/deeppurple/32/1551_2.png) [@DeepPurple](https://community.goactuary.com/u/DeepPurple)\ **Post date:** [December 15, 2022, 12:02am UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/99 "2022-12-15T00:02:09Z")
> [@SteveGrondin](#): > > Some use cases are 50 x 720, so small, the largest are 600K x 50, so bigger. Pivot table this. It’s a no-brainer. Pivot table are meant for this. And to be perfectly honest, Pivot tables are old news. All the cool kids are using PowerBI for this crap now. ---
**Author:** ![1695814](https://community.goactuary.com/user_avatar/community.goactuary.com/1695814/32/38506_2.png) [@1695814](https://community.goactuary.com/u/1695814)\ **Post date:** [December 15, 2022, 12:16am UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/100 "2022-12-15T00:16:13Z")
I despise pivot tables. They’re too volatile, imo. jmo. ---
**Author:** ![DeepPurple](https://community.goactuary.com/user_avatar/community.goactuary.com/deeppurple/32/1551_2.png) [@DeepPurple](https://community.goactuary.com/u/DeepPurple)\ **Post date:** [December 15, 2022, 12:24am UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/101 "2022-12-15T00:24:23Z")
They’re not my favorite either. And I tend to only use them when the input data is very static and unchangeable. But SteveGrondin’s initial question was an exact scenario _ **begging** _ for a pivot table. It’s like he asked a question like “I have this nail thing, it’s a long, narrow piece of metal with a pointy end and an end with a flat head. I need to push the pointy end into a piece of wood, but only up to the flat head. What is the best tool for the job?” [Previous page](https://community.goactuary.com/t/dumb-excel-questions/5148.md?page=4) [Next page](https://community.goactuary.com/t/dumb-excel-questions/5148.md?page=6)