True, I guess it would be easier to just put the media directly in the file.
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.
Staying abreast of the latest & greatest Excel has to offer?
1 Like
New topic: Multiple Windows of same Workbook
I sometimes have multiple windows of the same workbook open (Alt-vieW-New).
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.
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.
1 Like
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”?
1 Like
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.
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.
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+.
2 Likes
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.
1 Like
Thanks. At least this verifies that I’ve got to do what I’m doing.
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?
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.
I asked chat.openai.com/chat…it 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
1 Like
-
The volume of data could affect my response. Are the rows measured in the original data in dozens, hundreds, thousands, or millions?
-
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.
-
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.
1 Like
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.
1 Like
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.
2 Likes
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.
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.
I despise pivot tables.
They’re too volatile, imo.
jmo.
1 Like
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?”
1 Like