User Profile
m_tarler
Bronze Contributor
Joined 3 years ago
User Widgets
Recent Discussions
Re: PLEASE HELP ME. Excel Time Formatting Not Working?
don't know why my previous post didn't show but what the others are trying to say is that you entered 28:44 but that is hours:min so that was 1day, 4hours, 44mins and showing hh:mm means you see 4:44 (the day is hidden). you need to use the format mm:ss under custom to SHOW mins:sec as noted by others, you need to ENTER the time 0:28:44 for excel to recognize it as hours:min:sec (if you only enter 28:44 excel assumes that to be hours:min) Alternatively you can enter it as 28:44.0 and excel will recognize that as min:sec and tenths of a secondRe: Conditional formatting based on a checkbox
In my online / web interface here is my Conditional Formatting (add new rule) window: notice the Fx all the way to the right, that is the use a custom formula. then you just need to use a formula like =$A1 where the $A needs to be the column where the checkbox is, and the 1 needs to match the first row in the "Apply to:" rangeRe: Hi, I need help. I'm creating a calendar, based on events at our farm, which are on different dates.
As always, mathetes is right that we are really guessing here and attaching a sample sheet or hosting one online (e.g. onedrive or google drive) would be a huge help. Based on your description I'm taking a guess and agree again with mathetes that your data format is the problem. So my guess is you have a sheet something like this: which is the problem so I created a helper sheet I called 'Dates' and converted the above to a more functional format with a date column followed by the event names: by the way the formula I used was: =LET(in,Events!B1:.Z99,c,COLUMNS(in), list,REDUCE(HSTACK("Date","Event"),SEQUENCE(c),LAMBDA(p,q,VSTACK(p,EXPAND(DROP(CHOOSECOLS(in,q),1),,2,INDEX(in,1,q))))), VSTACK(TAKE(list,1),SORT(DROP(FILTER(list,TAKE(list,,1)<>0),1)))) then i created a calendar like this: where the box under each date used a formula like this formula which was in B7 (under the day 6 box): =TEXTJOIN(CHAR(10),1,FILTER(TAKE(Dates!$A$1#,,-1),TAKE(Dates!$A$1#,,1)=B6,"")) I will try to attach the sample file separatelyRe: Simplifying cost calculation using array instead of IF statement
unfortunately this is a double post. Here is the solution I gave there: I can't edit that sheet so can't tweak things but what I think you want is something like this: =SUMPRODUCT(VSTACK(J8/H4,I8/H4,1,1,1,1,1),XLOOKUP(J4,B3:E3,B4:E10,E4:E10,-1)) so the VSTACK will line the AG, AU up with the corresponding rows in the table and the rest of the 1`s will just sum those values. The XLOOKUP will select the corresponding column based on the AG%. I based this the best I could interpret what you did in the equations you have in that sheet. but if not quite right hopefully will at least give you some ideas on how to make a more 'efficient' equation.Re: Why can't I post my reply to this thread
are you attaching a file? this forum has an automoderation feature that seems to be a bit over zealous (IMHO) and doesn't allow many posts. Even from very established members that have hundreds or thousands of posts. It is rather frustrating if not down right infuriating. I created a reply to the overlapping time post with a complicated formula and a lengthy explaination of each line of the formula to only have it disappear. I hoped it would show up a day or so later (which sometimes happens) but it didn't, others already replied with other solutions and I have no desire to re-write it. That said just the other day I replied with a macro based solution. I got smart and broke the reply into 2: 1 without the file attached with images and code snippet and the 2nd with the file attached. So the first was no problem and the 2nd posted but without the file attached. I don't get it. Sry I'm not more help, but more in the same boat.Re: Move repeating columns into rows
The prior answers show how to restructure the data using Power Query but to your question of keeping the comments that are in the cells when reformatting the structure I'm pretty sure you would have to use a custom macro for that. I created one here and will try to attach a sample file next: Sub reformat_table() Application.ScreenUpdating = False Dim in_table, out_cell As Range Dim repeating_starting_column, repeating_count As Integer Dim ws As Worksheet Set ws = ActiveSheet Set in_table = ws.Range(InputBox("Select table range with repeating columns", "Input Table Range", Selection.Address)) Set out_cell = ws.Range(InputBox("Select cell for where output should be placed", "Output Table Location")) repeating_starting_column = 0 + InputBox("Column number where repeat begins", "Repeat Start") repeating_count = 0 + InputBox("Number of Columns that repeat", "Repeat Count") in_table.Range(in_table.Cells(0, 0), in_table(0, repeating_starting_column + repeating_count - 2)).Copy (out_cell.Cells(1, 1)) k = 2 For i = 1 To in_table.Rows().Count - 1 For j = repeating_starting_column - 1 To in_table.Columns().Count - 1 Step repeating_count If (in_table.Cells(i + 1, j + 1).Value2 <> "") Then in_table.Range(in_table.Cells(i, 0), in_table(i, repeating_starting_column - 2)).Copy (out_cell.Cells(k, 1)) in_table.Range(in_table.Cells(i, j), in_table(i, j + repeating_count - 1)).Copy (out_cell.Cells(k, 0 + repeating_starting_column)) k = k + 1 End If Next j Next i Application.ScreenUpdating = True End Sub If you highlight the source table before running the macro it will default that address range, then it will ask for the destination (cell), then the column where the first of the repeated columns starts (e.g. 2 in this example), and finally the number of columns that get repeated (e.g. again 2 in this example).
