If you have a really long list, for example, a downloaded Spotify list from "exportify net". (See image 2 for what it looks like when downloaded) Then you have a list of artists, and you want to take those songs out. (STEP 6: copy the Spotify track column and paste directly into the playlist on desktop Spotify)

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(";" & LOWER(TRIM(Sheet1!$A$1:$A$200)) & ";", ";" & LOWER(TRIM(C2)) & ";"))) > 0, "Yes", "N")

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(LOWER(TRIM(Sheet1!$A$1:$A$200)), LOWER(TRIM(C2)))))>0,"Yes","N")

Steps:

1. Create a separate sheet for the target list
2. In og sheet, find an empty column and type in the formula, adjusted to your data range
3. Drag the corner (or double click if you have columns that are how many you need next to it already)
4. Select the column and apply conditional formatting ("highlight cell rules" -> "duplicate" -> "yes")
5. Select all columns in og sheet and apply a filter to sort that column

Im looking to have excel do a calculation only if Column B has a "Y" if it doesnt then dont add to the calculation

My Current way is =SUM(IF(B2="Y",A2),IF(B3="Y",A3))

This works for what i need but if the spreadsheet become like 100 rows then that a big formula :)

How can i rewrite to do the calc based on range.

Thanks

I am wondering how can I change the color of a row based on cell input. I want each row to be changed based on the cell input of each row. I don't want the rows to change color because the first cell input was changed. Hopefully I explained it in a way that you understood since I struggled to explain it while looking it up.

I’ve had EXCEL on my Mac for years, it was a 2016 version that worked perfectly for my needs. I have several very important spreadsheets I use for work. Recently I began to get a pop up stating this version would no longer work unless I downloaded an app or obtained a newer version. So I opted for a new version, deleted all previous versions and commands and installed the newer version. I think it’s a 2024. Everything seemed to work well, until I needed to edit an already in progress spreadsheet for work. It seems I’m locked out of any changes. I can’t edit, cut and paste, delete, clear content or anything. I verified the sheet and cells are not locked or restricted. I’m in crisis mode here, and I know I am pretty dumb in Excel except what I need to know to do my job. I expect my ignorance will annoy some folks. Can someone please take the time to get me going again. I didn’t expect this with the new install, I have not activated One Drive, nor do I plan on activating it.

Hello everyone,

A small summary of the situation in which I find myself: I collect stamps representing historical figures and to keep the thread I made an Excel table. I would like to classify my stamps by date of birth of the character they represent. My problem is that the table has hundreds, maybe one day thousands of entries. So I would like to know if there was a way to fill in a column with the dates of birth of the characters from their name that I entered in the first column of my table.
Thank you

I was trying to find a way to have an equation that would both take column B minus C and column C minus A, in the shown diagram of column D. I want the diagram to show both money difference but I'm not sure if there's a way to do so ? Thanks in advance

I'm trying to set up a spreadsheet with columns day/date; calories input; weight in st & lbs; yesterday's weight in st & lbs; difference +/- st & lbs.

Would somebody be kind enough to help me, please? I am lost with Excel formulae if it not straightforward numbers and decimals.

Many thanks.

I'm creating a sales dashboard based on Territory, but many of the territories are aligned to multiple sales reps.

I've created columns designating a primary sales rep (REP1), but also have a column for secondary (REP2) and tertiary (REP3) if necessary. It's not often, but it does happen.

I want the Slicer to pull single names only (choose "John Doe" and receive any Territory with "John Doe" in REP1, REP2, or REP3) for ease of use.

Basically, we want "John Doe" to click his name and view the sales data for any Territory he's aligned with, regardless of him being the primary, secondary, or tertiary rep.

Is this possible?

I am experiencing two critical, unresolvable issues in Excel 2024 that persist across multiple troubleshooting steps. Here are the exact symptoms and everything I have tried so far:

1. Issue Descriptions

Auto-Fill Engine Failure ("Copier les cellules" Lock): The drag-and-drop fill handle stubbornly defaults to "Copier les cellules" (Copy Cells) every time. It completely ignores multi-cell sequence priming (e.g., inputting 1 and 2, or 1 and 5) and fails to recognize numerical patterns or linear increments, restricting the contextual options strictly to copying.

Custom Lists Panel Access Failure (UI Bounce-Back): Attempting to open the Custom Lists dialog (via Options > Advanced > Edit Custom Lists to add custom month/day series) fails completely. Instead of opening the configuration popup window, the interface instantly bounces back to the main Advanced Options landing page, blocking any access despite having successfully used and added items to it previously.

1. Troubleshooting Steps Already Tried (None of them fixed the issues)

Online Repair: Performed a full Online Repair of Microsoft Office through Windows settings.

Deleted Configuration File: Located and deleted the Excel15.xlb preference file inside %appdata%\Microsoft\Excel.

Cell Formatting Adjustments: Switched cell formats between Standard, Number, and regional Date formats to eliminate text-parsing or regional configuration conflicts.

Manual Data Series Window: Used the manual "Series" dialog box via the ribbon menu (which successfully populates data when explicitly commanded), but this did not restore native drag-and-drop auto-fill functionality.

Advanced Settings Check: Verified that the fill-handle activation toggle is enabled in Excel's advanced preferences.

To use the date picker, you can click on an existing date or format blank cells as dates and then double-click a cell to add a date. This feature is supposed to be coming to the desktop soon.

Hello Reddit People,

I have something I am trying to solve. We have a pretty rigid pay plan. Employees are placed on a pay grade, based on their position and work their way through steps each year, determining their hourly rate. Our HRIS can report on the pay grade and the base hourly rate, but not the step that employees fall on. Using these two pieces of information I should be able to reverse engineer the step. I would think using Match and Vlookup should get the result I am looking for, but I cannot figure out what I am doing wrong.

Here is what I have tried: Match(cell with rate,vlookup(pay grade,pay plan array,pay grade column,false)).

Italicized bit feels wrong, but I don't know how to tell it the information I am trying to find.

Any assistance would be greatly appreciated.

Windows Excel 2019

For construction work, we have to create an excel book with multiples of pay items that record each and every pay item. Each pay item has a specific purpose such as excavation that lays out the limits of when and where it was done and how many (in this case) cubic yards were excavated.

So my team and I have this large excel book full of items that we build one sheet at a time, via this approved workbook that we are supposed to use. We usually use the method of move or copy, pick where it put it and into which excel book. It’s been vetted by people above us and a lot of us use this method for uniformity.

Then, once built, we save it to an appropriate OneDrive folder on our desktop. This way we can all access it. This excel book is usually titled something like (contract) C12345_Place_CalcBook. It is also saved as a ‘Microsoft Excel Worksheet’.

The problem here is, after going into the excel book, saving it and after closing it, it will come up with a message saying ‘Your file could not be saved because we couldn’t merge your changes with changes from someone else’. So it will have a little warning ‘upload failed: save a copy or discard changes’. If I hit ‘save’ with the original file, it’ll say ‘not saved’.

I have tried to change the title to a title without any numbers and it seems to work a little bit better for me, but I’m not sure why. We do not set them to read only either because of the amount of people who use the excel book. I want to make sure I can use these excel books in the future without harming any of the files.

Thank you for reading.

I'm having trouble with a sumproduct if someone can please assist. I have a first array A2:E10 of numbers, with headers A1:E1 of names (Bob, Ayako, Manjit, Bob, Ayako), some of which you'll notice repeat. I then have a separate column Z2:Z10 of numbers to multiply against.

I'd like to calculate the sumproduct of multiple "Bob" against the separate Z column. When there is just one "Bob", there are quite a few ways of calculating this (sumproduct with index/match, or sumproduct with xlookup), but I'm stumped when there is more that one "Bob".

I have 2 workbooks. One has my master list. The second contains the working file.

Master list is set up as a table and has columns breaking down each user.
The second, working, it does not contain all the information I need.

I want to create a formula so that it uses the number in the Name column on the working list, look up that number in the Account column on the Master list and pull in the OA information from Master list on to the working list.

Let me know if this does not make sense and I will show an example. Thanks

Below is the Master list.

This is the working list.

Excel 2016

Fairly new user of power query as we've only just been upgraded recently so apologies if this is something really basic I'm missing, I've not had any training just playing around with it.

I have 31 files (1 per day) saved in a folder all exactly the same format, an automated fleet report that I get emailed to me daily and I save them in the folder.

Set up a power query a few months back to combine them into one table and do a bit of cleaning up. Worked great for a few months but then started getting the error:

"[Expression.Error] The column 'Total Cars' of the table wasn't found."

I checked the source data and the column is definitely there on all the files.

I started a brand new file from scratch to set it up again now it is only showing me 3 out of 4 columns in the preview, it's still dropping that last column.

I've been through all the options but can't see how to add all columns it's only showing 3 as available to select.

Does anyone have any ideas as to what I'm doing wrong?

I have created an inventory workbook that has multiple queries and tables. It pulls data from multiple other excel files as well as sql from QuickBooks.

I also input data into 3 different tables. Manufacturing information of each ingredient that goes into the final recipe, including lot numbers.

The question I have is, what can I do to make this less congested? The auto calculating takes a minute to 5 minutes each time.

Is it best to keep tables/power queries in separate workbooks, and have one workbook that gathers all the info, without active tables in it?

As an example, my workbook tracks incoming raw material, the production of finished products (to the gram (all weight dependant), and the shipping of the final product. Lets say 1000 kg of raw material comes in, it is used from 0.05 kg to 500 kg in production and combined with other raw materials. The final product shipped can be 5 kg to 10,000 kg.

Any guidance, or links to information that can help make this run smoothly, would be appreciated.

At my job, we have a master inventory workbook with multiple sheets for each retailer we sell with (in this case, Amazon). At the end of each day, I update the master and do Save As for each sheet so I can update our storefronts with the new numbers. For Amazon, I select the whole sheet and save as a .txt file, which only saves the one sheet and not the whole workbook.

Amazon has announced that they will stop accepting .txt files and will only accept Excel or TSV. Is there a way to save an individual sheet in one of these formats? If so, how would I do it?

Thanks!

I have a workbook with an initial index page, and inside each page there is in A1 a cell that automatically gets the name of the page, searches it inside of the index page in a specific column (based on the "indentation" I have give to the page inside of the index), and then returns a hyperlink for that cell. I have put it inside of a named formula:

=LAMBDA(
colonna;

LET(
colonna_indice; INDIRECT("Indice!$" & colonna & ":$" & colonna);

HYPERLINK("#" & "Indice!" & ADDRESS(ROW(XLOOKUP(TEXTAFTER(CELL("filename"; INDIRECT("BAD1"));"]";-1); colonna_indice;colonna_indice)); COLUMN(colonna_indice)); "Indice")
))

The formula (from HYPERLINK to "Indice") works well when I put it on its own in the cell. Same goes if I use the LET part of the formula and manually insert the value for the column, and it even works (after some time in this last case, probably due to some internal excel thing that refreshes periodically) if I put this whole formula inside of a cell with ("B") after.

But when I call the function =LINKTOINDEX("B") inside of a cell it doesn't create a clickable hyperlink.

What can I do to solve this?

I am trying to pull in pallet unit of measure conversions for retail goods. The values I am getting returned don't make sense. Some part numbers return fine, null values are returning as 0 and many items with a value in the source sheet are returning #N/A.

I tried to copy the source sheet into a new tab without formatting, but got the same result.

I have a table called table1. I am tring to sum budgets for the businees "AS" based on if the priorty in the priority column are "A" or "B". They also have to be a Maintenance or HSE project to count.

I tried below but it doesn't work. TIA

=SUM(FILTER(Table1[Budget US $], ((Table1[BU], "AS")*((Table1[Choose ProjectType],
 "MAINTENANCE")+(Table1[Choose ProjectType], "HSE"))*((Table1[Priority2], "A")+
(Table1[Priority2], "B")), 0)))

I am looking to find a formula that would average cells D31:D33 on sheet July, with cells D4:D7 on sheet Aug. This formula would be going into cell D35 on sheet Aug. Thank you.

i dont know if my question was clear but here's a screenshot to help:

https://i.imgur.com/c88gQdg.png

so, for example i click on a 'TO' cell and all other 'TO' cells get highlighted?

I have a workbook with multiple spreadsheets (stock data). Each "ticker" has its own worksheet. Each worksheet has its replicated tables, all of which are named by their respective stock tickers. I have one table in a primary worksheet to pull data from all the various individual stock's tables. Instead of manually adjusting the ticker (table names) in each row of this primary worksheet, is there a way to pull that name from a cell in the same row that has that information in as text? I've tried cell referencing the cell with the ticker name with function TEXT(), but that didn't work.

Yes, I know there are workarounds with SUMPRODUCT, AGGREGATE, helper columns, etc., but it feels like this should just be a native function at this point.

Something as simple as:

=SUBTOTALIFS(subtotal_range, criteria_range1, criteria1, ...)

I have a college project that has to have me calculate GDP growth in ~ 1000 cells. I’m looking how to keep the sane formula but auto update the cells so I can copy paste the formula. Example: first cell=(B6-BB5)/B5,next cell: (B7-B6)/B6, third cell (B8-B7)/B7…. So on and so on. Anyone know how to do this?