excel

44514 Topics

Name Manager using for create name which is contain atleast two name.
Hello. I creating somthing in excel with tables. These tables has thier own name, and can be dynamic or not. Any way. I using list in cell to select each data from a tables column. For that I created names with the content of a column. Unfortunatly i had to make a kind of name which is contain atleast 2 columns. These can grow dynamicly. So I think a plus helper tabel is not the right sollution for that. I think about 3 kind of solution. 1. =UNIQUE( VSTACK( INDEX(Tabla1[#Data];0;MATCH("Type_name";Tabla1[#Headers];0)); INDEX(Tabla2[#Data];0;MATCH("Column_name";Tabla2[#Headers];0)) ) output #name? 2. =UNIQUE(VSTACK(Lista_A; Lista_B)) lista_A as a name output #name? 3. created a helper table with the existing names. Created another name and use the table in it. output was the elements contained by the helper tabel, and the lista_A and lista_B tables each dedicated column. So How can i create list for a cell or cells from two column which are dynamicly growing, without a user needs to touch the core modell? Best Balázs
RábaiBalázs
Dec 18, 2025 Place Excel
42Views
1like
2Comments
Calculate overlapping hours
Hello, I need to report how many hours a staff member supervised one or more volunteers. For a very simplified example, Volunteer Name Date Start Time End Time Supervisor Fred 1/1/26 1:00pm 3:00pm Lucy Ethel 1/1/26 2:30pm 4:30pm Lucy Here 4 volunteer hours were served, but because there was a 30 minute overlap, Lucy only supervised for 3.5 hours. Is there a way to get Excel to calculate that? To say: look at all the entries with matching date and matching supervisor, and add up non-overlapping time. I'm not expecting this to be possible, but I thought I'd ask. Thanks!
volunteersfpl
Dec 18, 2025 Place Excel
24Views
0likes
2Comments
Return of the Corkscrew with Spilled Arrays
I'm starting a new topic but do want to refer to PeterBartholomew1 post on accumulators here https://techcommunity.microsoft.com/t5/excel/ways-of-performing-accumulation-with-dynamic-arrays/m-p/2329035 This post was in 2021 and things have or may have moved on since then and I'm wondering if there is an easier way to achieve the following. The Corkscrew. I have seen a number of addins and other 5G functions which do far more than I want. I'm basically looking for a way to break the circularity in a corkscrew and it seems that if I can calculate the top row ie typically the opening balance without referring to the closing balance then all would be good. To add complexity I’m trying to fuse the actuals and forecasts in this single function. I have flags to determine the actuals and can use NOT actuals to get the forecasts (same flags) and can pic up the actual opening balance from the source data easily. As I will calculate all time periods for both I can then just add them (as actuals will be multiplied by the Actual flag and thus zero for forecast periods and vice versa for the forecasts. So I just need to calculate the forecast period which will be the same for the entire array block. The key is. getting the previous value. (for the inflows and outflows as they are already calculated I can just shift their arrays forward I col but adding a column in front to get the previous value so that is easy. I can then get the closing balance by adding the inflows and outflows that would fall in the middle of the corkscrew to this previous value to simulate an opening balance. The inflows and outflows can then be added easily and the closing balance simply summed. So all the clever work would go in the opening balance. The added complexity I have is that I am doing a multi row version. ie there are ‘currently’ 3 entities all with opening balances, all with inflows and outflows and closing balances. I want to calculate these in blocks of these entities – 1 row per entity but in a single dynamic array. But the corkscrew will be made up of 4 dynamic arrays – opening bal, inflows, outflows and closing. If someone has a better logic here then do let me know. I saw Jeff Robsons one from a few years back using sumifs for the middle bit. But again, I want to do these in separate blocks which does add a layer of complexity. The reason is that I can ( have) modelled with multiple entities together rather than separately which means one just needs to extend the blocks (VBA to the rescue as there will be a good number of them) as required and inputs can just be assigned to the respective entity (often locations around the world might just be a tiny rep office with hardly anything going on so no need for a fully blown model but can be detailed separately. I also have the ability to enable or disable entities so they are included or excluded from the calcs. SO back to the problem, Model attached. Blue cells mark the dynamic array function for the block. The inflows and outflows will come from other dynamic arrays. But this idea means that one can expand the middle if the top/bottom are taken care of with regard to the circularity. I’m trying to get something easy to implement ideally in a single function (with or without LET)
Solved
James_Buist
Dec 18, 2025 Place Excel
2KViews
0likes
33Comments
Logical test for same text string existing anywhere in both ranges.
Hello. I have a Table of film credits, including the names of directors and writers. Some films have multiple directors (up to 3 individuals), whose names are in columns F, G and H. The writers' names (up to 4 individuals) are in columns J, K, L and M. I want to test for whether the film has a writer/director - e.g, one of the director names in the range F:H is the same as one of the writer names in the range J:M. I have created a column O to contain a formula with a logical test returning Y if there is a writer/director present. I tried =IF(Table4[@[Wri1]:[Wri4]]=[@Dir1]:[Dir3],Y,N) but this returns a spill error. Can anyone help?
Extopia
Dec 17, 2025 Place Excel
32Views
1like
1Comment
Microsoft Excel ODBC connection has stopped working
Since Microsoft rolled out the new Office 365 patch (19426.20186), the ODBC connection in Excel has stopped working. Neither existing queries nor new queries are functioning anymore. I kindly ask for your assistance.
Mario1111
Dec 17, 2025 Place Excel
169Views
0likes
3Comments
Excel toolbar format
Hello everyone, I personalized my toolbar with the shortcuts I use the most but the format of the icons varies quite a lot as shown on the picture above. Some icons are small and others are big. Is there any way to choose which ones are small and which ones are big? Or maybe make them all the same (small or big). Version is Microsoft Professional 2021 Thank you for your time
borgesl76
Dec 17, 2025 Place Excel
15Views
0likes
0Comments
How can I display negative values for time in calculation results
I'm trying to create an Excel spreadsheet with a column that shows the time relative to a predetermined point in time in 15-minute intervals with negative values for time in the cells above 0:00 and positive values below. I have the correct formulas entered in, but I can't seem to figure out how to get Excel to display negative values for time. Is there a way to do this?
South_Jeresey-240
Dec 17, 2025 Place Excel
230Views
1like
7Comments
Initializing TextBoxes within a UserForm
My spreadsheet contains a macro which utilizes two UserForms, each containing several TextBoxes. I want to initialize several TextBoxes in each Userform. One UserForm works and the other one does not, even though I have set the code up identically in each one. Here is what I have detected: When you step through the macro code (F8), when the first UserForm.Show statement is executed, the control skips to the first line of code for the UserForm and when the second UserForm.Show is executed, the control skips to the first TextBox on the Object. Here is a sample of the code I am using: Private Sub UserForm1() TextBox1.Value = "ABCDE" End Sub I have been writing macros like this for over 30 years, but this is the first time I have run into this situation. Any help here will be greatly appreciated. Al
aekbus
Dec 17, 2025 Place Excel
68Views
0likes
4Comments
Finding time duration between a start date & time with end date & time
Hi all! I'm looking for any formula or power query to calculate a total time duration within a day, given the start date, start time, end date, end time. Most of the dates will equal the same but there are some with the end date being the next day. I'd like to be able to exclude any overlaps as well. Currently, I have a large embedded IF formula: =IF(AND($G4=$O4,$H4<$H5,$P4>=$H5,$P4<$P5,$H4<$H3),$P4-$H4,IF(AND($G4=$O4,$G4>$G3,$H4<$H3,$P4<$P3,$H4<$P3,$P4<$P5),$P4-$H4,IF(AND($G4=$O4,$H4>$H3,$H4>=$P3,$P4>$P3),$P4-$H4,IF(AND($G4=$O4,$H4=$P4),0,IF(AND($G4=$O4,$H4<$P3,$P4<=$P3),0,IF(AND($G4<$O4,$H4<$P3,$P4>$H4),($P4+1)-$P3,IF(AND($G4=$O4,$O4<$G5,$O4<$O5,$H4<$P3,$P4>$P3),$P4-$P3,IF(AND($G4=$O4,$G4>$G3,$H4>$H3,$P4>$P3,$H4>$P3),$P4-$H4,IF(AND($G4=$O4,$G4<$O5,$P4>$P3,$P4>$H5,$H4>$H3,$H4<$H5),0,IF(AND($G4=$O4,$O4=$G5,$H4<$P3,$P4>$H5,$P4>$P3,$P4>$P5,$P2>$P3),$P4-$P2,IF(AND($G4=$O4,$H4<$P3,$P4>$P3,$P4>$P5),$P4-$P3,IF(AND($G4=$O4,$H4<$P3,$P4>$P3,$H4<$H5,$P4<$P5),$P4-$P3,IF(AND($G4=$O4,$H4<$H5,$H4<$P3,$P4>$P3,$P4>$P5),$P4-$P3,IF(AND($G4=$O4,$O4=$G5,$H4<$P3,$P4>$H5,$P4>$P3),$P4-$P2,IF(AND(ISBLANK($O4),ISBLANK($P4)),0,IF(AND($G4<$O4,$H4<$P3,$P4<$H4),($P4+1)-$P3)))))))))))))))) This seems to work for the most part but there are a few that I just can't get. I also pulled up my query and started to enter in the time durations manually and it couldnt come up with anything automatic for me. There must be an easier way for me to do this other than trying to create an IF formula for each answer that turns up incorrect. I have a screen shot below.
slbloyd17
Dec 17, 2025 Place Excel
64Views
0likes
2Comments
Welcome to the Excel Community
The Excel Community is a place we've built for all of you. You can learn more about how to do something with Excel, discuss your work, and connect with experts that build and use the product. With over half a billion Excel customers, we want to engage with you in fundamentally different ways and the community is a starting point for that. Our community helps answer your product questions with responses from other knowledgeable community members. We love hearing feedback and feature requests from you which helps us build the best version of Excel ever. If you have found an outage or a bug please post at our Answers forum. We look forward to getting to know you! Sangeeta Mudnal & Olaf Hubel on behalf of the Excel Team
Sangeeta Mudnal
Dec 16, 2025 Place Excel
63KViews
29likes
81Comments