Yeah, if you look at books by American authors (like John Walkenbach and others), they use a comma (,) in their formulas instead of the semicolon (;) we're used to. It actually tripped me up quite a bit back in the day too.
I can't believe I'm actually doing this... just putting myself out there. Is it weird to share? Maybe. But here we go! says: Hey there! How's it going?
So, I've got this massive spreadsheet where the first column is packed with product codes and the second one holds all the product descriptions. Is it just me, or is this happening in my spreadsheet too? It keeps popping up right there in the table... So, I’ve been seeing this pop up quite a bit lately: multiple product descriptions that are identical, yet they all have different SKU codes. It’s one of those things that looks simple on the surface but can actually be a massive headache if you don't handle it right. Why does this happen? Is it just bad data entry, or is there a deeper logic to it?That's why I also have this smaller table set aside, just containing my selected product codes.
Wait, where exactly are these duplicate descriptions popping up? Are we talking about the second column, or is this happening further down in columns C, D, and so on?
Larry Peterson30 said:Now what? I need to get a filter working in this massive spreadsheet—specifically for that first column with all the ID codes. Any tips? What if you just used more codes at once? I mean, why not just set the entire small table as your criteria? It’s only about 15 codes total, right? Might be easier that way!
If the descriptions match but the IDs are different, what’s actually the issue? Why not just use the dropdown menu in the filter settings? You can just check off those 15 specific codes you're looking for. Just go to Data > Filter on your header row, hit that first column, and select those 15 codes. Easy, right?
I'm sorry, I'm finding this a little hard to follow. Or maybe it’s just me?
=IF(F27>B27;"OVER BUDGET";"") (-> ignore that little symbol, it's not part of the math) - I actually meant to type the letter F there instead of G. I did that on purpose just to see if you were paying attention—don't get mad at me! The formula works perfectly fine regardless. Just listen to what Walter Jackson48 is saying and figure out the right combination for your needs. If you have even a basic grasp of English, Excel has built-in help features. Seriously, go read up on it; there are examples for pretty much every formula out there. Trust me, when you actually take the time to learn how things work, it might be a steeper climb at first, but once it clicks, you're set for life.🙂 (Speaking from experience here... it took a lot of grinding). Best,
You could actually structure that explanation using a formula like this: =IF(G27>B27;"OVER BUDGET";"") -> feel free to change the text inside those quotation marks to whatever you want!
Just pop that formula into cell G27 and then drag it down the column. Once you've entered the formula, click back onto cell G27 (where the formula is now sitting). When your cursor turns into that little black cross in the bottom right corner of the cell, just grab it and pull it downwards. Oh, and make sure to clear out that "NO" from the field first! Wherever the budget has been blown, that custom text will pop up (plus you'll get that red color formatting Benjamin Wilson7 mentioned)
So, you could just split that text file into two chunks—say, 40,000 rows each—and import them one by one. You’d save the first part as 1-40,000 and then do the same for the second part, covering 40,000 to 80,000. You can't really dump everything onto a single sheet here; you'll need to load the second chunk specifically into Sheet2.
Depending on how many columns you're dealing with, you could always merge them back into one sheet later by placing them side-by-side, starting right next to the first set, if you really want them all in one place.
Hopefully that makes sense? If not, I honestly don't know how else to explain it, sorry! But this is the way to go. I used to handle similar stuff with .dbf databases back in the day using Excel 2003, and with .lst files too.
The thing is, Excel 2003 is capped at 65,536 rows, while Excel 2007 can handle up to 1,048,576. So, why not try importing it in two separate batches? Since you're dealing with 80,000 rows, you could just split that text file into two pieces and load them one by one.
Look, you don't strictly need to do this with cell C1 on the Inventory sheet, but honestly? It’s got its perks.
Just head over to your Records sheet and click the cell where you want that total to show up. Type an equals sign, then jump back over to the Inventory sheet and click cell M41. Hit plus, click M42, and hit enter. (Now, if you just click M41 and drag your mouse down to M42, you'll get a different kind of formula—but you'd have to type =SUM(
So, here is what the formula looks like on the Records sheet: =Inventory!M41+Inventory!M42
Or, if you prefer using the SUM function: =SUM(Inventory!M41:M42)
Actually, I’d suggest putting those numbers in separate columns on the Inventory sheet instead (like column M and column N). Why? Because then it’s way easier to copy the formula from the Records sheet—you just grab that little black cross and drag the formula down. When the numbers are stacked vertically like this, it gets a bit more complicated, doesn't it?
If I’m following you correctly, you mean in Walter Jackson48's formula we should swap out A1 for "A", B1 for "B", C1 for "C"... you were a little fuzzy there, Walter Jackson48 🙂
- So, the values go into columns A, B, and C, right? And then in column D, you want the letters A or B to show up? Is that it? - But what happens if all the cells have a 3 or a 4—or even just two of them? What’s the actual rule there?
- I'll wait for some clarification. I really don't want to spend my time playing guessing games!
I’m actually looking to take this a step further and turn it into a "before print" and "after print" type of workflow. Basically, I want it to print the sheet with all the cell colors stripped out, but then have everything snap back to normal right after the job is done. Does anyone know how to pull that off?
Thanks so much!
Just record a macro while you perform those steps manually, then tweak the code a bit to clean it up. Once that's done, just drop a button onto the sheet and assign the macro to it. Easy enough, right?
Samuel Price58 said:Just trying to get some practice in for my college courses, working through this one little task in Excel 2007.... [/url]
To me, this looks more like a formatting issue within the cells themselves. Since all his other formulas are working just fine, I don't really see how regional settings could be the culprit here.
Try copying the formula from cell F20—the one that actually works—into cell H16, and then just manually adjust the range to fit what you need. If the working formulas are using semicolons as separators, then trying to use commas won't work. Honestly, I haven't run into an Excel setup that uses commas for summing unless you're following a specific guide by an American author.
So, from what I can gather, you really need to enable the Macro if the document you're trying to open comes from a reliable source (just make sure to follow step 4), and here is how you do it: Go to -> Word Options -> trust center -> trust center Settings -> Microsoft Office Button -> Word Options -> trust center -> trust center Settings and then select Macro Settings. Click The options that you Want : 1. 1.Disable all macros without notification Choose this if you just don't trust macros at all. All macros in documents and security alerts about macros ara disabled. If you happen to have certain documents with unsigned macros that you actually do trust, you can just move those files into a trusted location. Documents in trusted locations ara allowed to run without being checked by The trust center security system. 2.Disable all macros with notification This is actually what you have set right now. Pick this if you want macros turned off by default, but still want to get those security alerts whenever macros show up. That way, you can decide whether to let them run on a case-by-case basis. 3.Disable all macros except digitally signed macros This works pretty much like the "disable with notification" setting, with one big difference: if a macro is digitally signed by a publisher you already trust, it'll just run. If you haven't trusted them yet, you'll get a heads-up. It gives you the choice to either run those signed macros or trust the publisher entirely. Any unsigned macros? They stay disabled without any notification. 4.. Enable all macros (not recommended, potentially dangerous code can run) Select this if you want every single macro to run. Honestly, I wouldn't do this—it leaves your computer wide open to potentially malicious code, so it's pretty risky.
Anyway, sorry if I'm totally off base here! It almost feels like you're having trouble getting Microsoft Word itself to launch, rather than just struggling with a specific document, but I don't want to go off on a tangent.
Look, it might not be the prettiest little script ever written, but hey, it does exactly what you asked for, right? It takes whatever is currently sitting in cells A1 through A500 and bumps the value up by one. Just toss this code into a module and then use the Assign Macro feature to link it to a button. You can tweak the settings in the visual editor to make sure everything fits your specific needs. I used column "F" to run the math—it calculates the +1, then wipes the formula away and moves those final values back into the A1-A500 range. If column F is already being used for something else, just swap out the letter for some empty column way off to the side. Oh, and if you prefer the data to look different, you can always change the alignment to "Right."
Seriously though, this Macro is about as basic as they come, but isn't that what matters? It gets the job done!
Sub Increase_colA_by_1() ' ' Sub Increase_colA_by_1 Macro '
' Range("F1").Select ActiveCell.FormulaR1C1 = "=RC[-5]+1" Range("F1").Select AutoFill Destination := range("F1:F500"), Type:=xlFillDefault Range("F1:F500").Select Copy Selection PasteSpecial paste : = xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False CutCopyMode = False Selection cut range Range("A1").Select ActiveSheet.Paste With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False end with end Sub End With End Sub
Nicholas King said:So, locking the macro isn't enough protection for you?
Nope, because I have specific cells left unlocked in my protected sheet so people can actually enter daily or monthly data. The rest of the fields stay locked because they're full of formulas doing all the heavy lifting—which means the sheet *has* to be protected. Once the data is in, I need to run a macro via a Button to refresh the report without having to manually unlock everything every single time.
The code provided by Ivanov above fixed that exact issue for me. I had managed to get my macro working, but the annoying part was that my sheet would just stay unlocked after the script finished running.
So, I was playing around with some VBA code earlier today. Check this out:
Const C_Pwd = "YourPassword" With ActiveSheet .Unprotect C_Pwd .PivotTables(1).PivotCache.Refresh .Protect C_Pwd End With
It’s a pretty straightforward little sequence, right? You just set your password, unlock the sheet, hit that refresh button on the first PivotTable, and then lock everything back up tight. Simple enough? It gets the job done without any fuss.
Same thing happening on my end. Sub UPDATE_PIVOT_PASSWORD() ' It refreshes the report in cell P1. ' Const C_Pwd = "789" with ActiveSheet Unprotect C_Pwd Just a quick tip if you're working in Excel: if you need to refresh that first PivotTable in your sheet, you can just use `.PivotTables(1).PivotCache.Refresh`. Simple as that! Is there any way to use .Protect C_Pwd? Wait, does anyone else get tripped up by this? I was looking through some code earlier and realized how much the syntax matters when you're trying to close things out properly. Specifically, when you're using that `protect C_Pwd end with` sequence... it's so easy to miss a step, right? If you don't wrap everything up correctly, the whole thing just falls apart. Does that happen to you guys too? Just one of those little things that seems simple until you're staring at an error message! end Sub
It looks so much simpler now! Seriously, thank you so much. It works like a charm—exactly what I was looking for. 🙏
The whole catch here is that I need other people to be able to run the Macro in this program, but they don't know my password (I don't share it with anyone—call me paranoid if you want).
After scouring through all sorts of forums (including some big ones overseas), I've finally hit the nail on the head: this seems pretty much unsolvable for now. And man, I really, really need this to work. What a total shame.
I need to lock my sheet with a password—keeping the data entry fields open, of course—but there’s a catch: I can't run the Macro I assigned to my Button, which is supposed to refresh my Pivot report.
Everything works fine when the sheet is unlocked. It's driving me crazy! I know I've dealt with these kinds of glitches before, but my old troubleshooting guide is lost somewhere in my files. Any ideas?
p.s. That Sobol guy in the link above is so annoying, $19 bucks for what?