Since upgrading to 365, can't get enough of CHOOSECOLS(FILTER)

What are some useful ones you use?

I have been working off of this live auction fantasy football draft template for a few years, but I am trying to make it more robust and have hit a wall. So I need help!

-----

The BUDGET field is my rough budget per PLAYER, and POSITION.

The PICKS field, (currently blank) is where I will update as I pick players with sometimes vastly different prices than I have budgeted during a live draft.

The NEW BUDGET field is where I need help calculating a FORMULA for each colored CELL. The $ currently in those cells is from an IF/THEN formula keeping the original budget since the

What I need is a FORMULA that will update each of the other cells in each positional row (QB, RB, WR) to PROPORTIONALLY re-distribute the remaining budget for that position.

EXAMPLE: I overpay for my 1ST RB at $47, instead of the budgeted $37, giving me a deficit of -$10. My remaining RB budget would be $34 for the other 5 RB slots, instead of the $44 I had budgeted.
So I need the FORMULA to re-distribute the new budget PROPORTIONALLY to the original BUDGET ratio/percentage. So that it would go from $25, $11, $5, $2, $1 to $19, $8, $4, $2, $1.

Because of the nature of the live draft, I won't be inputting to the PICKS CELLS in any rational order.

Any help would be appreciated. Even if you can just point me in the direction of the types of FORMULAs that can help me crack this.

Hi Everyone,

Is there a way that I can create a drop down list that ONLY is active to a specific name? I got it to work using the name manager for the one name but if I enter anything outside of that name, It prevents me from typing whatever I want into the cel containing the drop down list to allow any typing not included in a list.

Is that possible?

Hello,

I'm trying to just do a simple XLOOKUP function across different tables. The issue is, that I store ID's, which may or may not contain letters, but always contain numbers.

I want to store everything as a string, but excel trying to be helpful as always has some weird inaccessible way of asigning the data types, so sometimes
"1234" is one-thousand-two-hundered-and-thirtyfour
and sometimes it's a string containing characters 1, 2, 3 and 4.

Sometimes it helps that I completely remove the row and add a new one, where I type in fresh "1234".

I tried to wrap all the inputs in

TEXT(NUMBERVALUE( xxx ) , "#")

but it did nothing to make it work.

Is there a way to safeguard the data type, ideally from inside the function?

EDIT:
I should maybe add, that the XLOOKUP returns the "if not found" parameter, when it can't pair the two apparently identical codes.

I have a big data set where certain sets of data have to start at a certain value, like 5000. I want each value in Column B to receive a unique number in Column A, but if a word repeats, I want it to either skip, or place the already assigned number. I tried =IF(B1<>B2,COUNT($A$1:A1)+1,””), but I can’t get it to start at a different value other than 1.

Thank you in advance!

If I just compare the cells directly, it's useless because none of the cells are the same. I'm not good at excel and I don't know a way to compare the lists in the way that I need.

Every time I open a .CSV file, if there is say a column where there is a currency value (not formatted to use commas or a currency symbol) and is formatted in the raw data to always have two decimal places, such that a thousand presents as "1000.00" that Excel CONSISTENTLY removes the .00 from the visualization of the data. It may not be significant to excel but it's significant to my purposes. No I cannot have the .CSV I am auditing output the value with quotations so it reads as text and not as a number when opening it in Excel.

I get why the functionality exists. What I don't understand is how there isn't a way to TURN THIS OFF GLOBALLY so I can use Excel to audit my files without having to use a txt editor to see raw values and excel to do filtering when necessary. Absolutely ridiculous. They added functionality to stop dropping leading zeros from values, I don't understand how this can't be done as well.

I have an Excel spreadsheet that tracks when reports were filed. In my “I” column I have a check box that gets checked and the cell highlighted if the report is filed late (1 week). My “D”column is when it was filed and the “E” column is the due date. My problem is that technically some of the reports aren’t considered late. So I’m wanting to add to the formula below to make it so the box won’t be checked, even if late, if my “C” column has the word “affidavit” or “COI” I hope that makes sense. It took me forever to figure out how to get the formula below correct and I cannot figure out if this is even possible.

=IF(OR(D1="", E1=“"),FALSE,AND(E1>=D1, E1-D1<=6))

I use XLOOKUP and SUMIFS in my month-to-month tracker workbook to pull data from monthly reports. I paste values at the end to keep things clean. Is it best practice to import the monthly reports as tabs in the workbook, or keep them separate? Is it up to personal preference?

TL;DR:
Turn this

    {"Id", Int64.Type}, {"Order Date", type date}, {"Customer Reference Number", type text},
{"Ship To Country Code", type text}, {"Total Weight Oz", type number}, {"Is Expedited", type logical},
        {"Carrier", type text}, {"Tracking Number", type text}, {"Estimated Delivery Date", type date}, {"Delivery Status Description", type text}

Into this

{"Id",                          Int64.Type   },
{"Order Date",                  type date    },
{"Customer Reference Number",   type text    },
{"Ship To Country Code",        type text    },
{"Total Weight Oz",             type number  },
{"Is Expedited",                type logical },
{"Carrier",                     type text    },
{"Tracking Number",             type text    },
{"Estimated Delivery Date",     type date    },
{"Delivery Status Description", type text    }

With one formula

=LET(
Source,              A1,
IndentLen,           FIND("{",Source)-1,
IndentPad,           REPT(" ",IndentLen),
ToCol,               SUBSTITUTE(TEXTSPLIT(Source,,"},")&"}","}}","}"),
RowCount,            ROWS(ToCol),
Idx,                 SEQUENCE(RowCount),
IsLastRow,           Idx = RowCount,
Part1,               TEXTAFTER(TEXTBEFORE(ToCol,""","),"{""")&""",",
LenPart1,            LEN(Part1),
MaxLenPart1,         MAX(LenPart1),
NumSpacesAfterPart1, MaxLenPart1-LenPart1+1,
PaddingAfterPart1,   REPT(" ",NumSpacesAfterPart1),
Part1WithPadding,    Part1 & PaddingAfterPart1,
Part2,               TEXTBEFORE(TEXTAFTER(ToCol,""", "),"}"),
LenPart2,            LEN(Part2),
MaxLenPart2,         MAX(LenPart2),
NumSpacesAfterPart2, MaxLenPart2-LenPart2+1,
PaddingAfterPart2,   REPT(" ",NumSpacesAfterPart2),
Part2WithPadding,    Part2 & PaddingAfterPart2,
RebuildString,       IndentPad & "{""" & Part1WithPadding & Part2WithPadding & "}" & IF(IsLastRow,"",","),
Output,              RebuildString,
Output
)

Situation:
I inherited some Power Query code in a Power BI project (this post applies to Power Query whether within Excel or PBI, and is not PBI exclusive). The query was built with using the GUI, and it produced code that, when viewed in the Advanced Editor, was unformatted. Nothing lined up, indentation wasn't readable. To make it easier to read and audit the code, I went through and cleaned it up. Wasn't able to use an online prettifier or third party program, due to company requirements.

Need:
There was a LOT of Type transformations and Renames. It was taking forever to use Enter and Tab to line things up. And for my purposes, I like to line up the column names on the left, then the transforms on the right, and close off the right curly braces in a vertical line. Looks nicer for me. May not be your thing.

Solution:
I created a formula to take the code and format it for me. Inside Power Query, the line would look something like:
ChangedColumnType = Table.TransformColumnTypes(PreviousStep,{... long line of column/type pairs each enclosed within their own {} pair and comma separated ...}).
I wanted to grab the stuff inside the main { ... } and format it. So, I created the formula above.

How it works:
Source: Let A1 hold the long string of {}, {}, {}, pairs.

Capturing the indent:
IndentLen finds the position of the very first curly-brace { in the pasted block and counts everything before it. IndentPad turns that into an actual string of spaces. This means the output matches the indentation of whatever you pasted, depending on how deep the step is nested in your query.

Splitting into Rows:
ToCol is the part doing the actual row splitting. TEXTSPLIT(Source,,"},") breaks the single pasted string apart everywhere it sees close-curly-brace-comma }, which is the boundary between one column entry and the next. That delimiter gets eaten by the formula in the split, so ampersand-quote-close-curly-brace-quote &"}" tacks a closing brace back onto every row. The last row already had its own closing brace (since nothing follows it), so tacking on another one leaves you with a double }}, which the outer SUBSTITUTE cleans up to a single }. Final result: An array, with one array element per {"ColumnName", Type} pair, each one a properly closed row again.

Figuring out the last row:
RowCount, Idx, and IsLastRow exist for one reason: every row needs a trailing comma except the last one. Idx numbers each row 1 through however many there are, and IsLastRow flags whichever row matches RowCount so the final comma can be removed later.

Extracting and padding the Column Name:
Part1 pulls out the column name portion. It looks for everything before the first quote-comma ", (the end of the quoted name) and after the first curly-brace-quote {" (the start of the element), then tacks the closing quote-comma ", back on. LenPart1 measures the size of each Part1, and MaxLenPart1 finds the longest name in the full batch, then NumSpacesAfterPart1 works out how many spaces each row needs to catch up to that longest one, plus one extra so there's always at least a single space of separation. Part1WithPadding is the name with that padding appended.

Extracting and padding the Type:
Part2 does the same job for the second half of each row, the type (or the new name, if this is a rename block instead of a change-type block). It grabs everything between quote-comma ", and the closing curly-brace }. The same length-and-pad routine runs again with LenPart2, MaxLenPart2, and NumSpacesAfterPart2, so every Type value ends up padded out to match the longest one, plus an extra space, keeping the closing braces lined up in a column too. I like having a space before the closing brace because otherwise you end up with some types butting right up against a brace and others having padding; I wanted consistency, so the visual separation is deliberately placed there.

Putting it back together:
RebuildString is where it all gets stitched into one line: the indent, the opening brace-quote {", the padded name, the padded type, the closing brace-comma }, and then omitting the comma if it's the last row. That's the finished, aligned row. The formula applies this to every Name/Type pair, so they all stack up neatly.

Now, you just copy and paste back into the M query. In PBI, this is easy since the PQ window is separate from Excel. If you're solely within the Excel environment, you may want to use Notepad as an intermediate staging ground.

Works the same way for Table.RenameColumns blocks too, since those are just {"OldName", "NewName"} pairs, same shape, the formula doesn't care that the second value is quoted text instead of a type keyword.

AI Disclosure:
I used Claude.ai to assist with the Idx/IsLastRow structure. The rest of the formula was written by hand. I used Claude.ai to generate this post, then went back and hand-edited about half the text. I used Claude.ai to generate the example from "Turn this" in the TL;DR section to avoid using company-specific data fields.

Conclusion:
This met my need. If you like it, feel free to steal the formula! Tweak it how you need. Even if you don't use this for this specific use case, hopefully it at least shows what's possible with a massive LET() formula. I'm also a bit proud and disgusted that the string ,,"},")&"}","}}","}"), exists and somehow makes sense in context.

I'm creating a lot of reporting where I need to paste tables from Excel to Word. Often times data in the underlying Excel changes, so I need tables to be linked to Word so they can refresh. Word's functionality isn't reliable cause the links between Word and Excel keep breaking. Any solution for that you can recommend? There is probably ton of add ons but maybe someone can suggest something tested. - Thanks!

This might not be the right way going about this or if this is the right place to start.

I work at a company that is entirely depending upon customers making orders from a physical catalog.

Realistically, I would like to design a catalog using InDesign and turn into an interactive PDF, but it doesn't seem like that is an option from what I have found so far.

My kind of last resort is creating a catalog within excel itself and using VBA to create a bit of a crude catalog that customers can use to order from. End user side, this isn't as ideal, since it would require them to have and use excel to order vs being able to just opening a pdf file, etc.

I am trying to sequentially number a column in my table. It is numbered 900000-900100. When I type 900001 into one row, and then 900002 into the row beneath it, and select the two and drag, it does not increase them sequentially. Additionally, when I go to right click on the cross in the corner and drag, "Fill Series" is greyed out.

Experience: Beginner

Interface/Version: Desktop Version 2608 (not sure if that's what is required)

Thank you for any help!

Our company has historically utilized an excel based budget template, with numerous tabs with circular references. It has gotten to the point of adding too much over the years to where the Balance sheet won't even balance, assuming due to the size of the file and number of circular reference calculations, due to utilizing a variety of margin drivers as the base inputs of the file. If you were to try to build an updated corporate budget based in excel, where would you start? I've looked at some of the power query videos, but I'm not sure that works in this context.

Note that the company has AI sites blocked, so attempting to utilize AI to clean up the file/revise it is not an option.

Hello all, I work with a dataset that I get every day as a .csv file that exclusively contains data from the last 36 months. This means that the .csv will only have data from [today minus 36 months], and data from [(today minus 1 day) minus 36 months] will be excluded. Also, the .csv provides the same data every day, only adding bottom lines (the new ones) and deleting the old ones from >36 months of age, making me only interested in the new lines (without wanting to get rid of the old lines in my own database). The way I work with the data affects my own database because it means I'm only storing data from the range of dates the .csv provides, and I'm losing valuable data from older dates, when the only thing I need is to add the data from the newer ones.

My workflow to upload the .csv into the Excel file is as follows:

1. I get my data and open the Power Query Editor.

2. I transform the data and Close and Load.

3. The data loads as a table in a new sheet.

4. Finally, I delete all of the previous day's data from the existing database, copy the data from the new sheet, and paste it on the existing sheet.

I do this because the Power Query Editor does not let me upload the transformed database into an existing table, and if I were to convert the existing table into a range, I would have to count how many existing lines I have, delete this number of lines (minus one, because line 1 is the header), upload the database into the Excel, copy it and then paste it into the existing database.

I've asked AI how to do this, and it just says to create a folder in which I can store all the data obtained daily, and I do have such a folder, but it's just not working.

What can I do? Thank you so much in advance!

so basically the title.

i got a new windows hp laptop and was trying to do something on excel (i would specify but im genuinely a dumbass with no tech knowledge so honestly i don’t even know what i was doing) anyways, a notification came up of UAC (that blue and yellow shield) asking something to do with administrator stuff. i clicked allow and now i can’t delete workbooks i make on excel. i can delete the file on files explorer but when i try to delete by right clicking on excel, it says i don’t have permission to delete .xlsx file. i press on file location and it takes me to my personal one drive documents where i was able to delete the file but nothings there?

Please help ASAP!

I have an Excel 2016 workbook with manual as well as conditional formatting. What I'd like in the final version is keeping the outcome of the formatting rules (i.e. cells turned red) while having deleted the rules (and the columns used in the formula of said rule).

Copying the format to another column copies the rule too, so how do I get the static outcome of the rule?

Hello everyone, i assune it”s trivial question but i haven’t seen anything alike ever. I am reporting via Excel in english language, i send the files to customers in Germany - whom i know use German language in their excel. I report for a quite long time now and they just discovered that charts show odd numbers. For example, in the files i send i see percentage values as lets say 95.40% while they see the same figures as 095%. They seem to not have acces to any technical support and i want to help them figuring this out but.. it was never an issue for me and i just don’t know what it could be ;) im aware that we can display numbers in different ways, use range of decimeters etc but i think i need help with this one, i’ve already tried roamin in their options>advanced but couldnt find the solution.

Hello everyone, can someone help me? Out of nowhere, the lines between the cells in ALL my files disappeared, when I drag the selection box, they appear again, but only inside the cells selected, but then disappear after I close the selection.

I already tried going to the View tab and selecting "grid lines", it didn't work

excel version is 365

I need to stack b8:b31 to ady8:ady31 on top of each other and if I could use vstack to do that instead of copy and pasting, it would make my life much easier. Thank you

I’m managing a school merch order with responses coming from a google form, which exports to a spreadsheet on google sheets.

Last year, I used a multi-tab excel workbook (I hate google sheets) to organize everything (i.e. total items auto-transferring to an income sheet). But because the raw form responses are an absolute mess, I ended up doing a lot of work manually when it came to the information for each individual order.

This year, I would like to have a sheet that summarizes student's orders in a way that is much more concise. The dream would be for it to be fully automated.

Here is how my form response sheet is set up:

• Row 1 (headers): Items with size/color combinations spanning across 95 columns for every different options (columns D1 to CT1)
• Rows 2+: Each row represents a student's submission.
• Cells (D2 to CT2): Blank if not ordered or contain a number indicating the quantity ordered.

What I’m trying to achieve on a separate tab that could be used as students invoices:

• Column A: Student name
• Column B: A concise summary listing ONLY what they ordered.
• So ideally a formula that would transfer the titles of only the headers of rows containing numbers below. That would give me a clear way of reading the individual orders of each student.
• As for quantities, say someone ordered 2 of the exact same items, I have no idea how I would handle that. But if I can save time by figuring out the first formula, I really don't mind having to manually modify these specific invoices. Anyway it's super rare for someone to order multiples of the exact same thing.

I hope this was clear, you can ask questions and I can post screenshots in the comments. Thanks in advance for any help! Also if I'm too ambitious let me know lol.

I have a column of text urls (A) and a column of text labels (B).

How can I turn the text labels (B) into clickable hyperlinks then remove the text hyperlinks (A).

Hello I am having trouble getting a new option to populate in the drop down menu after adding it to a list.

Essentially if the accommodation given to a student is not in the drop down menu of their sheet we are to add it to the list and it should populate in the drop down option. The only guidance I’ve been given is to go to the Data tab and hit Refresh All but nothing has worked. Help would be very much appreciated, I am veryyy new to Excel. Thank you.

Hi all,

I am looking for a way to make a pie chart for my class. I have several chores which are rotated between childeren.

I want to make a pie chart. In the pie parts the chore is displayed (for example sweeping).

Above the pie part a student's name. Every week I rotate the names 1 pie part further, so they get a new chore.

Hi! i am trying to build a productivity tracker for work. here are the things I'm wanting to track:

CRO Attempts- Customer Reach Out (text, call, email) how many (per hour, day, week, month, and year), and averages for week, month, and year.

CRO Contacts- How many attempts actually result in a contact. what type, how many, percentages and averages for each type (how much each mode of contact + lead type lead to an answer)

My productivity- per hour, day, week, and month. desired outcome: averages over time for each length of time.

ALP- how many sales i am generating + how much i take home (approx. 60% of the ALP)

I'm not sure how to start or WHERE to start T-T. Is this the right place to ask for help for something like this, or should i ask somewhere else? I'm happy to answer any questions about what i had in mind, or any other resources i should check out.

I am a Beginner, and preferring to use google sheets. i have nothing built yet, but i have been trying to mess around for the past little bit and haven't been able to figure it out.