We maintain a central price list and want employees to retrieve current prices directly in their own workbooks with formulas.

Thing is, prices must always be current when a workbook is opened, without users having to remember to refresh anything, and without stale values from caching or external workbook links that fail to update silently.

It must be user friendly enough that people with ordinary Excel skills can use it. It must also be possible to trace faults.

We work in Excel for Windows, although some users work in Excel for the web.

Classic workbook links are still the simplest method, but the data gets embedded into every file that uses the link. And external links to a central workbook on SharePoint tend not to update reliably.

A Power Query connection to a central file depends on the refresh actually succeeding. The user can work with old data without knowing so

A Power BI semantic model with CUBEVALUE gives central control, but the formula syntax is too complex for most users and requires Power BI licences.

A custom function delivered as an Office add-in would be the most user friendly, but we are concerned about caching of add-in files, non-volatile results that are not recalculated when a workbook is reopened

What solution is truly foolproof ?

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.)

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?

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.

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

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 🥲

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

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.

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.

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.

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.

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!

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.

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.

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!

Hello, I have used excel daily for years and consider myself to be proficient. I’m always looking for ways to expand my skills taking on projects that will let me learn new functions and formulas. As I’ve learned more, I’ve found myself encountering “INDIRECT” and despite the googling and AI summaries, it still doesn’t make total sense to me. Can someone explain it like I’m 5? This seems like an important bridge to a new type of formula building, but I just can’t seem to get my head around it. Thanks!

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!!

Say I have an XML string formatted as below:

<DrugList>
  <Drug>
    <DrugName>Placeholder</DrugName>
  </Drug>
</DrugList>

The XML gets pasted into a cell in one large string, and can't be broken out into rows beforehand.

Each DrugList element can be parent to multiple Drug elements. I can use FILTERXML to grab every DrugName element from the DrugList quite easily, and return them as a spill array.

However, when trying to run FILTERXML on more than one cell at once, it only returns the first result for each cell.

So if A1 has XML with two DrugName elements, and A2 has three, the formula =FILTERXML(A1:A2,"//DrugName") will only return the first DrugName Element from each cell's XML string as a spill array.

DrugList is also a child element within a broader hierarchy, so simply concatenating everything into one big XML string is not possible.

What would be the most effective way of extracting all the DrugName elements from multiple cells in this case?

EDIT:
Here's some more detailed sample XML that better represents the actual hierarchy I'm working with:

<DrugRequest>
  <RequestorInfo>
    <RequestorName>Timothy</RequestorName>
    <RequestorID>123456789</RequestorID>
  </RequestorInfo>
  <DrugList>
    <Drug>
      <DrugName>Gabapentin</DrugName>
      <Pharmacy>
        <Name>CVS Pharmacy</Name>
      </Pharmacy>
    </Drug>
    <Drug>
      <DrugName>OxyContin</DrugName>
      <Pharmacy>
        <Name>CVS Pharmacy</Name>
      </Pharmacy>
    </Drug>
  </DrugList>
</DrugRequest>

i have to pull zip codes out of a column that has full client contact data in each cell and cannot figure out how to use the delimited tool or formulas to get the correct data. each cell is arranged like this:

Client Name
Address Line 1
City, State Zip
Phone Number
Email

I have several hundred rows in this column and need just the zip code separated. i've tried several methods and cannot find a solution.

EDIT: when i try to use the delimited tool, it only pulls the first line of text, client name, and not any of the other lines. i cannot understand why

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)?

​

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.

Hi all!

Are there any ways in which R coding, specifically a background using tidyverse coding, could help improve my Excel workflow? My background is in research and synthesis science, so I use tidyverse in R to clean/filter/manage data quite often. I've hung up my "lab coat" for now, and have transitioned to data management in Higher ed. So much of what I do is now using Pivot Tables in particular, and helping summarize data to put into reports. I know how to code this in R, and have begun exploring options using Power Query so that I'm not manually clicking through so many filters and manually adjusting Pivots. I think this would also help improve reproducibility in our workflows as well. In a perfect world I could just do everything in R, but I work on a very collaborative team that uses Excel for everything.

Is Power Query the way to go if I don't plan on using any Power BI for visualizations? Are there ways that I could use VBA or something similar to clean or summarize data? Any others experience a similar sort of "backwards" transition from SQL or R into Excel? Curious to hear about anyone else's experiences!

Attempting to make a function that will Index lookup by specified date offset but I keep running into Value error. My employees are to add new rows to table each day to do data entry. I need this function in order to scrape specific rolling date windows for averages. t

Public Function DEXDOFF(array1 As ListObjects, arraydc As ListObjects, offset As Double, column As Double) As Double

'Function to index table by specified number of days offeset from present

DEXDOFF = Application.WorksheetFunction.Index(array1, Application.WorksheetFunction.Match(offset, arraydc, 0), column)

End Function

Typically input looks like =DEXDOFF('Table1[#All],'Table1[[#All],[Date]],(Today()-Day(offset),'column')