User Profile
m_tarler
Silver Contributor
Joined Nov 28, 2023
User Widgets
Recent Discussions
Re: Overlapping times within one row
you can try this: = (endTime2 > startTime1) * (startTime2 < endTime1) and if you want it to be specifically T/F insteas of 1/0 then just = (endTime2 > startTime1) * (startTime2 < endTime1) = 1 note this does assume all your times are the same day and each end time > start timeRe: "Clear Filter From" Option is Never Available
not saying you don't have a problem but the image you show doesn't look like there is any filter applied and hence the 'clear' option is grayed out. Basically the drop down butting is showing a sort icon NOT a filter icon. The list shows (Select All) as checked (i.e. none are filtered out). Maybe you could apply a filter and show us what it looks like when a filter is applied.Re: Cross Referencing Data in Excel
alternatively =TOCOL(IF(ReferencedDocuments=DocInput,Documents,NA()),3) which using sheet ranges in Oliver's sheet would be: =TOCOL(IF(C2:AP162=C166,B2:B162,NA()),3) and BTW, I just saw your response and if you are getting a blank in col C and #CALC! in column E that is probably because the value you are searching for (C166) is not found. Make sure you have entered the text EXACTLY as it is in the search range. as for "not an accurate list" what does that mean? please note that I see in column C the formula is only copied down 14 cells so that should be copied down more rows (but for the 365 solution that won't matter)Re: I need Help with Cell Formatting to be able to copy
there are 2 good options: a) my preferenced is to NAME that 7% cell. you can do that by clicking on the cell then clicking on the name of the cell to the left of the formula bar and typing a name: here is highlighted D10 and then highlighted the name to the left of the formula bar then typed a name for that cell: alternatively you can go to Formulas -> Name Manager and add new name: either way now when you use that name in a formula it won't change when copied so =A4 - A4*Mpercent b) simply prefix the row/column that shouldn't change using $ so = A4 - A4 * $D$4Re: excel
this is a duplicate post to: copying formulas but I totally understand as this forum can do funky things with messages and it is easy to think it didn't actually post your message the first time. here is my answer there: first off that is NOT a number that is a string value referring to a Sheet Name. There is no default built in way to increment that Name. That said here are some work arounds you can use: a) INDIRECT function: =INDIRECT("'" & 725+xxx & "'!$G$64") where xxx is some function that will output what you need to increment by. For example if you are copying down a row starting on row 14 it could be ROW()-13 so it would look like: =INDIRECT("'" & 725+ROW()-13 & "'!$G$64") or =INDIRECT("'" & 725+ROW(G1) & "'!$G$64") the same could be done for COLUMN() or even SHEET() if this will be copied across sheets. b) TOCOL , TOROW, VSTACK, or HSTACK functions: so if you want a column or row of these values and presumably those sheet names are of a consecutive sheets, then you can simply use: =TOCOL('725:799'!$G$64) if they are NOT consecutive sheets but follow a standard pattern like '725', '725old', '726', '726old',... you can then do the same thing but FILTER or DROP the unwanted values. For example if they are every other sheet you could use: =DROP(WRAPROWS(TOCOL('725:799'!$G$64),2),,-1)Re: copying formulas
first off that is NOT a number that is a string value referring to a Sheet Name. There is no default built in way to increment that Name. That said here are some work arounds you can use: a) INDIRECT function: =INDIRECT("'" & 725+xxx & "'!$G$64") where xxx is some function that will output what you need to increment by. For example if you are copying down a row starting on row 14 it could be ROW()-13 so it would look like: =INDIRECT("'" & 725+ROW()-13 & "'!$G$64") or =INDIRECT("'" & 725+ROW(G1) & "'!$G$64") the same could be done for COLUMN() or even SHEET() if this will be copied across sheets. b) TOCOL , TOROW, VSTACK, or HSTACK functions: so if you want a column or row of these values and presumably those sheet names are of a consecutive sheets, then you can simply use: =TOCOL('725:799'!$G$64) if they are NOT consecutive sheets but follow a standard pattern like '725', '725old', '726', '726old',... you can then do the same thing but FILTER or DROP the unwanted values. For example if they are every other sheet you could use: =DROP(WRAPROWS(TOCOL('725:799'!$G$64),2),,-1)Re: Can we get more guidance and feedback on the moderation being done on the messages
Hi Allen, so it appears some update happened and I'm not sure if it is better or worse (I think better maybe). So now I keep getting errors for including some illegal html but I only used the tools provided in this dialog edit box. If I paste a screenshot I get errors, if I use the [Insert/Edit Code Sample] I get errors, if I use the <Attachment> I get errors. It doesn't happen every time and I try to copy and then paste-values only (ctrl-shift-v) and sometimes that helps but it is again, very frustrating trying to use this forum with so many bugs OOOF, I went to post and got an error on the above TEXT-ONLY!!!! Here is a screenshot...Re: Providing info on tab, based on other tabs with parent child relationship.
I think it is the 'double lookup' that you are having an issue with. I did it using both structured references (tables) and normal workbook references: structured reference needs to be adjusted for each column =IF(SUM((TRANSPOSE(FunctionRole_matrix[@[Sweep]:[task-z]])="x")*(TaskTool_matrix[Broom]="x")),"x","") normal sheet ranges need to be copied down the rows =IF(MMULT(EXPAND(1,1,ROWS($A$31:$A$37),1),(TRANSPOSE(F11:L11)="x")*($B$31:$H$37="x")),"x","") that said you can use MAKEARRAY or REDUCE or THUNKs with LAMBDAs to make them all in 1 formula Here are the results: I will try to attach the sample file if this system lets meRe: Api in every row of excel.
you can use Excel built in tools to pull the data. you can create an external link using Data->get data from webpage and pull a table of data but not sure how well that would work here. alternatively, you can also use inline formula like this: =--TEXTBEFORE(TEXTAFTER(WEBSERVICE("https://api.mfapi.in/mf/"&$A2&"/latest"),"nav"":"""),"""}],") where A2 is where the Mfund Code is located NOTE: I do NOT see that Stanley Index fund anywhere in that apiRe: Inserting Graph Breaks/ Axis Breaks
A graph or axis break doesn't exist. As for workarounds, it is unclear which graph you are using and what you mean by "a space" between points. If you have a literal gap between line segments then presumably you have an X value with no Y value and if that is the case then you could use a helper column to filter those blanks out. If you are using a Scatter plot with data in one range and then data in another range which may be 1 data set or broken up as 2 different data sets then you could: a) probably best and easiest is to create 2 graphs and put them adjacent. you can even play with formatting to make them look like 1 with a break. Here is an example I worked up for my guess at the sort of situation you mean. I have a sin wave from 1-100 and then again from 800-900. the upper graph is just a straight plot, the lower is 2 plots side by side: b) you might be able to use a helper column to force the values / graph you want using interpolation and then use a line graph with predefined 'category' labels. Although possible it would be a bit of work and likely result in the 'data points' not being the actual 'data points' since you may have to use interpolation and would only work in cases where interpolation between points is possible (i.e. in the above the line between x labels of 0 and 20 is not a single interpreted line). This would be best for cases where you are taking samples/values at regular intervals with a gap. Here is a very simple case the upper plot is a scatter plot of the data (on the left) and then the lower is a Line Graph of the data on the right. In this case the data are the same but in theory you could transform the original data as long as it can be represented using discreet x points:Re: MO 365 Excel - Difficulty Changing a Date format
if you have tried various date formats and they haven't changed, my guess is they are actually TEXT that just look like dates. You can try DATA -> Text to Columns and just next through to step 3 and select Date: MDY and then Finish. After that the data should now be actual dates and you can change the format accordingly (under 'more number formats' and then Date and yyyy-mm-dd is listed 3rd on my app or you can go to custom and specify yyyy/m/d for the format you listed but note I believe the international standard (ISO 8601) is actually yyyy-mm-dd (and there are some variants allowed but I don't believe the use of / is one of them).Re: Formula for adding multiple sheets
So first off, the way your workbook is set up is the main issue. So often people like to split entries across sheets based on months or years or even customers or employees but it is much better and more efficient to have all the data entries in 1 table (or sometimes linked tables like a table for employee info and another table for employee sales records where an employee or employee number in the sales records 'links' to a line in the employee info table). That said there are some cases where you can't get around this workbook structure (e.g. it is exported from something else and you don't have control over that). But if you can you should combine all the data entry tabs into 1 entry table and add a column for whatever variable you are using to define the tabs. For example, let's say Sheets 1:5 are for Stores 1:5 then just create a single table and add a column named 'Store' and enter the corresponding identifier. Then it is easy to filter that table for a specific Store or data entry like John Doe (or is it Joe Doe?). You can even still have individual tabs for each store and just put a pivot table or use a formula like =FILTER( TableRange, CHOOSECOLS( TableRange, StoreColumn# ) = Store# ) you can also then again create a pivot table based on the person/place and it will put out the sum values. Excel was designed to work this way and works well like that. So that all said if you can't fix the workbook to make your life easier going forward, then you can use a trick like VSTACK to 'stack' the data from all the tabs and then apply the FILTER or either GROUPBY or PIVOTBY functions to get your result. Here is a simple example: =LET(data, VSTACK(Sheet1:Sheet3!A2:G100), GROUPBY(CHOOSECOLS(data,3),CHOOSECOLS(data,5),SUM)) and here is the output:Re: Creating weekly calendar template that has overlapping positions
Hi CDoescher4 , I'm glad this is helpful. I have some questions on how to make the changes you are hoping for. First off your PM and this post don't agree on the color coding: AEMT: Blue / Blue EMT: Pink / Pink EMR: Pink / Green Driver: Green / Yellow Ride Along: Green / Red The coloring isn't hard, I added 5 conditional formatting rules to make the names be colored based on their role. As for how a single person/shift goes from slot 3 to slot 2 and back to slot 3, that is an unfortunate consequence of how I do the functions, BUT I also think it is a necessary side-effect because if you notice in the image below Courtney S goes from slot 3 to slot 2 and back to slot 3 AND Morgan L goes from Slot 4 to 3 and back to 4 AND you also have Arielle D that overlaps that timeframe also. Let's pretend Arielle D was a "Driver" (and not an EMR) then if we had Courtney S going straight down column 3 and Morgan L going down slot 4 then even though there is an opening in slot 2 then Arielle D (assuming them as a "Driver") couldn't get added because slot 2 is restricted from allowing a "Driver" role. So I already did an order precedence based on roles and then how long they are on shift that day so in theory it should lessen that since someone on shift for only 4 hours is less likely to cross the transition of someone working 12 hours (as opposed to the other way around). I did update the formulas because I didn't take into account "Ride Along" and when I did I also created the precedence order to be AEMT, EMT, EMR, Driver, Ride Along but if you prefer I can change that up to be AEMT, EMT/EMR, Driver/Ride Along (e.g. the second column would list an EMR with >shift time before EMT).Re: Creating weekly calendar template that has overlapping positions
Dear CDoescher4 , it was a bit of work but I think I have something that may work for you. Here is the basic output: so each day has 4 columns (because you said 4 doesn't happen very often but yet the data you gave appears to have 5 people signed up for the first part of the first day. It will fill in each timeslot per your rules prioritizing AEMT then EMT/EMR then Driver and will 'prioritize' people with more hours that day over those with less. I don't know what "colors" you would want and where. As you can see on Tuesday you have AF in slot 2 with slot 1 open because they are not an AEMT. The formula is far from trivial using multiple LAMBDA functions and are locate in the upper left corner of each day (i.e. cells B4, F4, J4, ....) and the dates in row 2 are based on the starting date in D1, which for now is just grabbing the first date from the data sheet. Here is the formula in case the file doesn't come through... =LET(data,Data!$A$2:.$H$99,times,$A$4:$A$99,date,D2, dStart,XMATCH(date,TAKE(data,,1)),dEnd,IFERROR(XMATCH(date+1,TAKE(data,,1)),ROWS(data)+1), in,TAKE(DROP(data,dStart),dEnd-dStart-1), sTimes,IF(CHOOSECOLS(in,5)<date,date,TAKE(in,,1)+CHOOSECOLS(in,5)),eTimes,IF(CHOOSECOLS(in,6)>date+1,date+1,CHOOSECOLS(in,3)+CHOOSECOLS(in,6)),roles,TAKE(in,,-1), todayData,SORTBY(HSTACK(sTimes,eTimes,TAKE(in,,-2)),eTimes-sTimes+24*(roles="AEMT")-24*(roles="driver"),-1), insert,LAMBDA(name,role,slots,LET(place,IFERROR(XMATCH(1,HSTACK(role="AEMT",role<>"driver",1,1)*(slots="")),0),IF({1,2,3,4}=place,name,"")&slots)), timeslot,LAMBDA(t,REDUCE({"","","",""},SEQUENCE(ROWS(todayData)),LAMBDA(p,q,IF((t+date>=INDEX(todayData,q,1))*(t+date<INDEX(todayData,q,2)),insert(INDEX(todayData,q,3),INDEX(todayData,q,4),p),p)))), DROP(REDUCE("",times,LAMBDA(p,q,VSTACK(p,timeslot(q)))),1))
