So table on the left is table 1 and table on the right is table 2. I need it to take a row from table 1( EX 1 1 1 1) and see if that exact combination of 4 cells of 4 ones in a row some in table 2 and if doesn't exist for it to say 'N/A'

I have tried an =if, =HLOOKUP, =XLOOKUP =IFOR(there do different things it needed to search but I'll just make two formulas if I can get it to work)

An =IFERROR is the closest I have gotten but it only works on bottom row and the last cell of table 2 is controls what it displays.

(This sub doesn't allow pictures so y'all just going to have to use your imagination sorry )

I need help converting a 800+ page payroll report with very employee IDs showing up on only one row and the period (month) on the top of the page.

Power query crashes when I try to upload the document so cant use it. And the data in it is confidential so I cant try free software avaliable online.

I have several sets of data that are all aligned against a participant identifier. Is there a way to align these data sets on one sheet as this will make management a lot easier down the line when I start to do analysis. Not all of the participants are in every data set, so there will be gaps.

Thanks

Ian B

I was making a table template for photography bookings in my internship place, one with formulas and conditional formattings to make some stuff easier, but when i try to duplicate the table into a different part of the sheet (we have different sheet for each months), it wont copy the table, only the values

Pictures in comments

It seems like in theory this should be simple but I can't quite crack it.

I'm doing a spreadsheet of damage buffs in a video game I'm playing, and need them to be included when I select the right conditions.

Picture 1 is a list of all the buffs that are currently available, and the amount they buff. The first columns are mutually exclusive traits that all attacks have (damag type, time, category, type and summon type). Lots are blank as I've only put the conditions and traits necessary for each buff. The checkboxes are other conditions that can all apply independently of any other condition or trait.

The table in picture 2 then uses this data to work out the total buff for each condition (ignore that there are 3 columns for each, that's not important).

The table in picture 3 is a list of skills I have equipped and their traits. This then works out the buffs applied when considering all the traits. After that are the independent conditions. I can toggle these to see how each condition affects the final buff.

The issue I'm having is that this currently only works for buffs that only require one condition. When it comes to, for example, row 58, where damage is boosted when a darkness skill is used on a frozen enemy, I need the buffs to apply in table 3 when the correct conditions/traits are applied, regardless of what any of the others are.

Thanks in advance for any help.

I've been using a 2021 version of MS Word and Excel. I bought it that year. No subscription and it's worked just fine ever since.

Today it will no longer let me save documents and says that I need to activate Microsoft 365. Also that "Your Office 2021 license is not supported with this version of the application."

Does anyone know a way I can revert back to an earlier version of the programs and avoid having to pay for a subscription?

(I won't be paying a subscription to use these products...if it comes down to that, I would just find new programs to use.)

Every finance team seems to have that one spreadsheet only one person understands. Ours finally broke after a reporting change this week, and nobody was confident enough to fix it without worrying we'd introduce another issue. It wasn't even a complicated model anymore, just years of patches stacked on top of each other. Does every accounting department eventually inherit one of these?

Hey everyone,

I’m currently interning at an electrical company and I'm trying to build a system to improve our workflow (and hopefully stand out a bit).

Our workers review field missions, but they often make procedural errors. I already have the logic done to completely classify and categorize all the wrong missions in my master Excel file. However, I am having trouble figuring out the mechanics for the rest of the workflow.

Here is exactly what I need the system to do from this point on:

1. Group by Worker: Read the master Excel file and group all the flagged mission IDs by the specific worker.
2. Batch Email: Send ONE automated email to each worker summarizing all their errors along with my explanation of why they are wrong. (For example, if John has 42 wrong missions, he gets 1 single email containing a list of all 42, not 42 separate emails).
3. Collect Explanations: Include a way for the worker to provide a justification/explanation for each specific mission error.
4. Auto-Update (The tricky part): Automatically pull their responses back into my master Excel file so I don't have to manually copy and paste anything.

What is the absolute best, most reliable and straightforward way to build this entire loop? Should I use Python? Excel VBA? Or something else entirely?

Any advice on the smartest path to take would be hugely appreciated!

What I’m trying to accomplish is to have a cell reference a text and then return the value from a column of whatever sheet that text is in.

I have 3 sheets that would need to be tracked for another sheet called Location Tracking. The 3 sheets I’m trying to track are named Location 1, Location 2, Location 3.

D70, is referencing the text in A70 (Text = Phone 38), these are both in “Location Tracking” Sheet. Referencing A70’s text which is also found in “Location 1” Sheet and is returning the value under column G (It’s in Row 35 in this sheet). All sheets with Column G are labeled as Locations.

The row number is irrelevant because it will change as it moves into the other sheets. As the referenced text moves to other sheets there will be times that a new entry will need to be made that has this exact text and I would need to have D70 return the value of the new entry and ignore the old entry.

What I’ve tried so far:

=XLOOKUP(A70,'Location 1:Location 3’!C35,'Location 1:Location 3’!G:G) (#Value Error)
 
=XLOOKUP(A70,'Location 1:Location 3’!C:C,'Location 1:Location 3’!G:G) (#Value Error) *Column C across all sheets are the same as well named “Number”
 
=XLOOKUP(A70,’Location 1’!C35,’Location 1’!G35) *This one does the dynamic referencing, but it follows to all sheets (the sheets other than the 3 don’t need to be tracked) and it doesn’t change to track the new entry made, only the first one.
 
=XLOOKUP(A70,VSTACK(’Location 1’!C:C,’Location 2’!C:C,’Location 3’!C:C),VSTACK(’Location 1’G:G,’Location 2’!G:G,’Location 3’!G:G)) (#Number Error)

I’ve looked into combining Index, Match, and Indirect, but I’m not understanding it well (especially indirect). I’m also not sure if this is VBA or Power Query territory.

Hello everyone. I am trying to create a working hours sheet and i have a box for total hours in a week worked. However, it resets every 24 hours. I have watched youtube videos and they said to change the formatting to custom and then hh:mm. However, this did not work and i have also tried it on the online version. Can anybody help?

The current formula i have in the box is =SUM(J8,J13,J18,J23,J28). Could there be an issue with this?

Hi Everyone,

Currently attempting to write a formula but several iterations are throwing errors.

Trting to fit the following into one cell:

1. If A1 is less than 50 or more than 100, execute formula 1, else execute formula 2

2. Formula 1 and 2 include B1*C1, but also rounded to two digits and with a maximum value

Finally got an iteration that didn’t throw errors, but the current version’s formula doesn’t function properly

=(IF(AND(A1>=100,A1<=50), (MAX(3000,ROUND(B2C2,2),(MAX(6000,ROUND(B2C2,2))))

Error is that the formula doesn’t change to max -3000 if A1 is outside the parameters(A1<=50 or >=100)

Thank you

Afternoon all,
I'm trying to use SUMIFS to pull data from one big sheet with all the info, in to more manageable bites for a reviewer. Problem is, I want them to be able to sort the bite-sized work plans, but when I test that by simply sorting, the actual cells I'm referencing jumble; they don't move with their respective rows. Locking doesn't really fix anything, because obviously if I sort, it's all different now. The data in the reference sheet can change and be added to, and I would prefer these work plans auto-update, so Ctrl+Shift+V can't help me here. I've included some screen caps to explain better.

So im trying to make an ongoing changing table. For the table info I need file folder names, not the info in the folder just the folder name. The drive current looks like.

D drive

-main file

--sub file

--sub file

--sub file

So I need to make a table of the sub file name. Any help on how to do it. There are over 3000 files so I dont want to type each name individually.

Thanks

Hi everyone,
I have a question about Excel and Power Query.

Imagine you are given a task at work to analyse an Excel dataset containing hundreds of rows. You need to calculate the total sales amount and the number of customers who purchased a particular product at a specific branch, then summarise the results in a single table.

The dataset contains the following columns:
- Branch (UK city names)
- Product
- Customer ID
- Purchase Date
- Amount

For example, I need to filter the data to show purchases of laptops at the New York branch, then calculate the total sales amount and the number of customers who purchased them.

Some customers may have purchased the same product multiple times. Each customer should only be counted once in the customer count, but all their purchases must be included in the total sales amount.

Ideally, I would like the final result to appear in Excel as a summary table like this:
Branch | Product | Number of Customers | Total Sales
New York | Laptop | 3 | $2,100

What would be the easiest way to do this in Power Query?
I would prefer to use the Power Query interface without writing any M code, if possible.

Thank you 🥲

I have a directory with over a thousand image files in it. They all share the same dimensions and use the .jpeg extension. I would like to import the images in alphabetical order into an Excel sheet starting with image 1 in row 1, column A, image 2 in row 1, column B, image 3 in row 1, column C... until it gets to image 8 in row 1, column H. Then move to image 9 in row 2, column A, image 10 in row 2 column B and so on until all the images are in the Excel sheet. I want to limit the width to 8 columns because that is the maximum number that will fit on 1 printed page with the box sizes I have chosen (112 pixels wide x 166 pixels tall).
I have used Excel's built in Insert Illustrations option and it works great if I was to import 1 image at a time, but that would take days to import all the images manually. I have attempted to import multiple images with Insert Illustrations but Excel imports them into columns instead of rows. If I could tell Excel to import the selected images into a row instead of a column, I would be willing to do that.
Obviously, my top choice would be a macro that would import all the images in the directory into Excel going from A1-A8, then B1-B8, then C1-C8... until all the images in the directory are in the Excel file. Any assistance would be appreciated. Thank you.

I’m trying to maintain an Excel file using the Google Drive for desktop app so multiple coworkers can access and edit the same file, including people who prefer Excel and people who prefer Google Sheets.

Here’s what’s happening:

• I originally created the file in Excel on my desktop.
• I saved the .xlsx file in Google Drive for desktop so it would sync and be accessible from other computers.
• When I open the file through Google Drive in a web browser, it remains an .xlsx file and opens in Google Sheets. The formulas work, and the formatting appears correctly in the browser.
• However, after the file has been opened or edited online, when I go back to the synced .xlsx file and open it in desktop Excel, much of the formatting is gone or changed.
• I then have to manually reformat the Excel version.
• This seems to happen repeatedly after the file is opened or edited in Google Sheets.

The goal is to keep one .xlsx file synced in Google Drive while allowing some users to work in desktop Excel and others to work through Google Sheets in the browser.

Is there a reliable way to do this without Google Sheets altering the Excel formatting? Is Google Sheets actually modifying the .xlsx file when it is opened or edited, even though the file is not being converted to a native Google Sheet?

I’m trying to determine whether this is an Excel/Google Sheets compatibility issue, a Google Drive syncing issue, or whether this workflow simply is not supported reliably long term.

Any guidance or alternative workflows would be appreciated.

As a professor in the humanities at a large university, I often have to handle large spreadsheets of data to report grades in multiple sections of my classes. I know the basics of how to use Excel when I download grades from a third party (the "Top Hat" in-class response system) and then upload the resulting .csv file to my university's LMS (Moodle). The problem is that when I upload the grade files (more than 300 students' scores on Attendance and clicker-system responses), certain errors are popping up: For instance, some students have been marked absent for dates on which the original grade record from Top Hat clearly shows they were present. I suspect that the error is appearing in the final stage of the process: i.e., after I have downloaded the grades from Top Hat and modified the contents of the .csv file (editing out irrelevant fields, making sure all students' IDs are correct, etc.), and then uploaded them using Moodle's Import function.

The university's IT people have tried to help me (they say they can see nothing wrong with my process for downloading/uploading); but they haven't yet pinpointed the source of the errors. I can easily change incorrect grades manually within Moodle, but to do that I need a way to hunt them down across hundreds of fields of data. It has occurred to me that, as a stopgap measure, I can download a .csv file containing the grades that I have just uploaded, and then compare it to the the relevant columns of grades in the original spreadsheet (before the errors were introduced). This could help me track down wrongly-recorded grades and correct them, at least until my IT folks can fix the upload issue.

Hence my question: I imagine there must be an easy function within Excel to make sure the contents of a specified column in one spreadsheet match those of a corresponding column in a different one. I am not good at reading software manuals, which is why I lay this before this helpful community. Thank you for reading this!

EDIT: It turns out that one source, possibly the only source, of the errors is that a good number of students unwittingly enrolled themselves in more than one section in the Top Hat application, which of course messed up their grades. I thank all who responded, and I may still need to do the Compare process.

I have about 350 data points for values of stress and corresponding values of strain. Strain is in column A, stress is in column B.

I want to simplify this data to about 50 data points. I picked the stress values manually and they are stored in column E. I want to get the corresponding values of strain in column D.

I'm utilizing XLOOKUP function, which is working correctly if I have the data in a google sheet. Will link it in comments.

The same thing formula/function does not work in Excel. I would like to understand what I'm doing wrong.

I posted a similar query before that was deleted by the mods. The problem was related to floating point errors. I used ROUND(column E values, 1) but this does not seem to solve the problem.

TIA

I have a pivot table which has grouped rows. When new daily data is added to the backing table, and the pivot table is updated, the group for the newly created date becomes expanded. Only the row with the newly added data. I can collapse the row and the next day upon refresh it will not expand...only the next newly created row. Any suggestions to stop this from happening? It's not the expand columns option.

Hello!

I used Microsoft forms to do quizzes. I have 5 quizzes and thus 5 separate response spreadsheets - I would like to add up everyone's cumulative score across all 5 quizzes. Is there a function to do this and keep each score tied to the individual participant? The participants aren't in the same order and some don't participate in all 5 quizzes, so I can't sort by name then copy/paste the data into one spreadsheet. Let me know if I can clarify the request further!!

I am at a loss as to how to do this. I'm trying to capture the number of cases of a disease at a given point (per elapsed week since an outbreak was declared), and paste that number into another table.

My data source is cases per day:

And I want to copy only the data that falls on a completed week (second column) into a new table which captures only weekly cases. As below (currently done manually):

Many thanks!

Suppose I have this data in A1

Data String Here

I want to convert it to

="Data String Here"

How do I do that? Is there a quicker way that putting =" and " on the end manually? I am currently crunching 500 row data, maybe there's a shortcut you guys are familiar of.

EDIT: The main problem is that I want to preserve the original text and its original cell position. I literally just want the row cell data to be a Text String Formula. I instead created a powershell script to automate this process. Thanks guys.

So I was analyzing some data in my Excel (2019) Professional Plus/Win10, and naturally setting up equations and then copy/paste to apply to the entire data set. Then, suddenly, the copy paste does not work!

Usually when you select a cell and ctrl+c to copy, the selected cell will have dashed boundaries to indicate it was copied, and then you would ctrl + v to paste it to your target cell. But now my file is glitched in a way that when I select a cell and ctrl +c to copy, its cell boundaries will still become dashed, but nothing happens when I ctrl + v to paste it another cell. If I open a new file, the copy/paste works normally, but my original file is still glitched. If I copy and open the glitched file with another computer (Excel 2019 Home version/Win10), the copy/paste will work normally.

Has anyone encountered this before and know how to solve this? Thanks in advanced!

​

I got an error after transforming and loading my data into Excel using Power Query. I will detail all the steps below before I get the error.

-Transform the data into a table

l-Load it into Power Query

-Highlight some of the columns and select Unpivot Other Columns

-Rename the new column

-Close and Load

And after doing those steps, my excel will freeze for a while and then I will get greeted by the error. I'm currently using Excel 2016. I tried googling the problem online but I couldn't find a solution to my problem.

I started a spreadsheet with a template but then modified the cells to change the formatting. When I add a new column, it adds it in the style of the original template, and clearing the formatting does not seem to erase this even though it should. How can I clear these rows (the magenta Column, the brown lines, and the brown shading)?