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! GiGi
Got 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 29Power 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?
Calculate "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?use 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!
Return 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.Excel 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?
