Forum Widgets
Latest Discussions
Why is MS-Excel adding an "@" to my formula and breaking it???
Hello Excellers, This is so annoying and it is breaking my formula. I hope someone has a good solution: In VBA I have these two lines: Range("N1").Value = "3/24/2025" Range("N2").Formula = "=INDEX(" & Range("E3:E" & lLastRow).Address & ",MATCH(MIN(ABS(" & Range("E3:E" & lLastRow).Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False) & "-$N$1)),ABS(" & Range("E3:E" & lLastRow).Address & "-$N$1), 0))" Now in N2 I get this formula: =INDEX($E$3:$E$1029,MATCH(MIN(ABS(@E3:E1029-$N$1)),ABS(@$E$3:$E$1029-$N$1), 0)) And I get an error in cell N2 and I will need to remove the @s and it works ad needed. This works and this is what I need and this is what the VBA code is supposed to put in cell N2. =INDEX($E$3:$E$1029,MATCH(MIN(ABS(E3:E1029-$N$1)),ABS($E$3:$E$1029-$N$1), 0)) This formula is going to give me the closest date after what I put in N1, and it works very well, I just need to tell Excel not to add the unwanted @ signs! Is that because E3:E $ lLastRow is considered an array? Thanks for any help! GiGiGeorgieAnneMar 22, 2025Iron Contributor12Views0likes1CommentWhen I paste a long number, Excel turns it into scientific notation and changes numbers.
Here's an example. I have a long number (it's from a license plate/bar code): 00100018320523710862 Whether I use my bar code software's export-excel function, or PASTE the number directly, here is what I get: 1.00018E+17 If I then format the cell as a number, I get: 100018320523710000 As you can see, I have lost the '0862' at the end of my number. Any ideas?SolvedKeith KarglMar 22, 2025Copper Contributor348KViews1like34CommentsGot in over my head, now I need formulas to get the right data.
A program creates an excel spreadsheet that has 2 columns, I need to know out of the 51 possible location points in column A, which ones are missing. And list those in say column D. This sample is obviously missing -001 and -002 so I'll know the result is correct when at least those 2 are there. Thank You, Walt -028-Floral Shop Wooden Post-028 37 -014-Sub Shop/Pizza Area-014 35 -021-Bakery Cold Case Area-021 34 -026-Produce Cold Case Salad Pkg Area-026 34 -025-Produce Cut Fruit/Veggie Area-025 33 -023-Produce Cut Veggies Case-023 33 -027-Produce Cold Case Organic Case Area-027 33 -024-Floral Portable Cutting Machine-024 32 -015-Marche/Soup Area-015 31 -010-Indoor Seating Area #2-010 29WaltsexclproblemsMar 22, 2025Copper Contributor100Views0likes7CommentsPower Query by Default Excludes First Blank Column
I have researched this down the rabbit hole long enough without any answers. In Excel I created a Power Query Get Data from Sharepoint Folders using the Contents method. There are about 10 files where the data is in the same format, columns match sheets are all the same name, etc.... FYI, None of them are setup as Tables for reasons I won't go into. They just can't. When I import the files 5 of the files have data in column A while 5 do not. When I import the data the files that do not have data in column A Power Query is automatically removing those columns from those sheets. Which then screws up the column order when the data is appended and loaded to the table. How can I force Power Query to bring in Column A even if some files column A are blank?heylookitsmeMar 22, 2025Brass Contributor58Views0likes2CommentsFormula for if a date is more than X days in the past
Can someone help me with a formula for Excel? If Column A returns a set of dates, is there a function to show if the dates in Column A are more than 30, 60, 120 days in the past?SolvedO_edwardsKPPB-9Mar 22, 2025Copper Contributor26KViews0likes5CommentsCalculate "The Wednesday" two weeks before due date
We currently run checks on Wednesday of each week. To show an "anticipated pay date", I want to somehow use the "due date" of the invoice and return the date two Wednesday's prior. Is that even possible? For example, if the due date is Thursday, May 1st, I want it to return Wednesday, April 23rd. If the due date was Tuesday, April 29th or Wednesday, April 30th, I want it to return Wednesday, April 16th. I know how to return "two weeks ago", but since I only want Wednesday's that makes it a little more confusing. Is there a "2 (day of the week) ago" -type formula?RandomPanda1933Mar 22, 2025Copper Contributor28Views0likes2Commentsuse SUMIF for cell in immediate row above
Can someone please help me? I've been using this formula for 2 years and now I need to add another word and row/path to the filter (not sure of correct terminology) =SUMIF($B$2:$B$6,"Tithes*",$G$2:$G$6) Now, I need to add OR "processing*" to "tithes" and it can only be for processing fees in the row directly below tithes & offerings. If "processing*" is in a cell directly below a "tithe*" cell, then it should be included in the formula in K25. (I can figure out K26 & K27 if I can get K25.) Can someone please help me create the correct formula? I would be extremely grateful!BeckyBo334Mar 22, 2025Copper Contributor54Views0likes2CommentsDelimiter to be used
Dear Experts, I have a data like below:- So, each sub Packets[x], if has data will have Common and Chain as below, I want to bring Chains in the same level as the common, like this:- What delimiter shall , I use, to achieve this. Thanks in Advance, Br, Anupamanupambit1797Mar 21, 2025Iron Contributor66Views0likes1CommentReturn multiple rows with matches
I am trying to compare a variable set of numbers that will be pasted in by a user with two different columns in a table and then outputting the rows that match in either column. I would like to eventually delete the duplicates this produces...but i cant even figure out the first part. I've tried combinations of V and X lookups, Index, Match and other random things to no avail. see below for an example of what I'm trying to achieve.mtiblierMar 21, 2025Copper Contributor59Views0likes2CommentsExcel 2021: Replace value in one cell based on the values of two cells (that cell and one other)
Greetings: I am using Excel to normalize some foreign-language bank data for my taxes. (Excel, and Notepad++ are excellent tools for data-conversion tasks!) Given the following data truth table Column A Column B [null] [null] <== no change !=null [anything] <== no change [null] !=null <== Column A should change to "XFR" In other words, if column B is not null, and column A is null, then column A should become the text "XFR" How do I implement this?jharris1993Mar 21, 2025Copper Contributor37Views0likes1Comment
Resources
Tags
- excel42,422 Topics
- Formulas and Functions24,600 Topics
- Macros and VBA6,378 Topics
- office 3655,985 Topics
- Excel on Mac2,636 Topics
- BI & Data Analysis2,352 Topics
- Excel for web1,903 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,622 Topics