Excel Formula changes values after a day or so
I have a bunch of text strings separated by commas in a cell (e.g., in COLUMN A here).
| Column A | Column B |
|---|---|
| AA,AB,AC | ?? |
| AC,AZ,BB | ?? |
| AB,BB,CD | ?? |
Each one of these texts is associated with values in a separate sheet called LEGEND.
| Column A | Column B |
|---|---|
| AA | 15 |
| AB | 20 |
| AC | 35 |
| BA | 90 |
I want the sum of the values (in Column B on first sheet) associated with the text strings. I have this Vlookup formula in my excel sheet at column B on first sheet =SUM(VLOOKUP(TEXTSPLIT(A1,,","),LEGEND!$A$2:$B$4,2,FALSE)). It gives me the correct SUM but the issue is that when I re-open my file after a day, it only returns the first value and NOT the SUM. e.g., when I run the formula first time, it gives me 70 for the first one but after a day when I re-open my file, the value is already replaced with 15. Please help.
I have tried Mutiple formulas to fix this, but it didn't help.
Well, did this and saved, closed & reopened and still gives results:
SUM(SUMIFS(B$9:B$12,A$9:A$12,TEXTSPLIT(A2,,",")))
Just to prove the points I made in the comment, see:
- How to make Colors Mixer in vba
- How to get HEX color value from cell color?
- SSIS: Insert multiple Excel files with same format but different file name to multiple database table destinations based on the Excel filename
- How to randomize Excel rows
- VLOOKUP Returns 0 even though there is a value in the line below the first result
- transpose a table with multiple values in excel
- Trying to late bind outlook to but getting type mismatch error after create object line
- Power Query Transform a Column based on Another Column
- Apply FILTER formula to array (and use filter criteria on specific column in the array)
- Bitly API call using VBA Excel Macro
- excel vba projects not closing
- In a standard MS Access SQL query output that does not have any aliases, how do I replace the full names by their "first-letters" aliases?
- Sigma or Summation function in Excel
- Problem getting the value from a cell using M in excel
- INDEX MATCH to also pull through notes
- Move formatted data in selection from Excel into Word tables
- Excel VBA function to remove letters at end of string
- Can't get all excel processes to stop when closing through Powershell
- Excel function to construct an array from values without VBA
- Alternative method to delete file with out having to add MS Scripting Runtime Reference
- Add new button to worksheet with Excel Add-in
- Apply outline border only to range of cells with openpyxl
- How to Separate Data with Inconsistent Patterns into a Structured Format in Excel
- Excel - Aggregate dynamically values in a Table using array formulas (MAP, BYROW, CHOOSECOLS, TEXTJOIN, LAMBDA, FILTER, HSTACK) in a single Cell
- How to reference the current User\Desktop location in VBA
- Powerpoint chart to excel relative link
- EXCEL: How do I return multiple columns using XLOOKUP, when the lookup_value is a range?
- VBA: How to Loop IF Statement through a Cell and Chart Series
- Incorporating Checkbox to lines of code rather than Cell value
- How do I remove a character from a column of data in Excel