Microsoft Office Q&A: General Discussion (Word, Excel, PowerPoint...)

Kenneth Carter27 said:So, it’s my very first day on the job and I’m already hitting a wall with a few things:

I've got this spreadsheet where I need to figure out someone's gender (I have titles like Mrs. and Ms.)—how do I actually pull that off? I'm looking at about 2,000 entries here.

Talk about a double whammy, right?

Hey everyone,

I’ve run into a bit of a situation here....

So, I have this setup with seven columns, where the top row lists out the days of the week—Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, and Sunday.
Below those headers, I'm using ones in the cells to indicate which specific days certain tasks are actually being performed.

What I’m trying to figure out is how to make it so that... if there's a 1 under Monday, the "week" cell just writes "Monday,"
but if there's a 1 under both Monday and Tuesday, the cell should ideally show "Monday Tuesday," and so on through the week....

I've attached a screenshot here, I suppose, just so it's a little easier to wrap your head around what I'm aiming for....



Uploaded with Imgur

The brute force method:

Pop this formula into cell N1: =IF(G1=1,"MON,")&IF(H1=1,"TUE,")+...

Or just go with =CONCATENATE(IF(G1=1,"MON,"),IF(H1=1,"TUE,"),...)

That CONCATENATE function works fine in LibreOffice, so I'm pretty sure it's available in Microsoft Excel too, right?
Also, in LibreOffice you use a semicolon as a delimiter, but if I remember correctly, Microsoft Excel uses a standard comma...

To stop those pesky "FALSE" errors from popping up whenever you have an empty cell—you know, like on a non-working day—you just need to tack a little bit of nothingness (that's the "" symbol for the tech-savvy out there) onto the end of every single IF function. If you do that, your formula ends up looking more like this:
=IF(C35=1;"Mon;";"")&IF(E35=1;"Tue;";"")&IF(G35=1; "Wed;";"")&IF(I35=1;"Thu;";"")&IF(K35=1;"Fri;";"") &IF(M35=1;"Sat;";"")&IF(O35=1;"Sun";"")

p.s. Just make sure you tweak the cell references to actually match your own spreadsheet setup. As for everything else, just follow the advice Michael Jackson10
already gave you
.

@Walter Jackson48/">@@Walter Jackson48, thanks for catching that! I totally skipped over the second half of the IF function, didn't even realize it was going to spit out a 0...

Michael Jackson10 said:@Walter Jackson48/">@@Walter Jackson48, thanks for catching that. I completely glossed over the second half of the IF function—I didn't account for it spitting out a zero...

Don't mention it, @Michael Jackson10/">@@Michael Jackson10. We were both just trying to do the right thing and help out here...

So... how do I... automatically apply a specific formula in Microsoft Excel?
For instance:
1. I have a formula sitting in H1—it’s linked to the data in cell C1.

I want to drag that same formula down column H for about 1,000 rows... but I need the formula to adjust its references as it goes.
Basically, the formula in H2 should point to the data in C2, and so on.

What’s the best way to auto-fill this down to row 1,000? I really don't want to manually update the cell references every single time—especially since I have three different conditions to manage here.

Here is the formula:
=IF($C$1="K7";D😁;IF($C$1="K8";E😈;IF($C$1="K9";F: F;"")))

Look, if I’m following your logic correctly—and that’s a big "if" given how we've been going at this—try setting your ranges like this: D1:D1000, E1:E1000, and F1:F1000. You know, instead of just selecting the entire column from D:D, E:E, and F:F. Once you've done that, just grab the formula and drag it down, or copy it, all the way to row 1000. That should work, assuming I haven't completely misinterpreted what you're trying to achieve here.

p.s.: For heaven's sake, leave out the emojis. It makes it nearly impossible to actually read the formulas when they're cluttered with icons.

Thanks!

Walter Jackson48 said:So, if you want to make sure those empty cells—you know, the holidays where nothing is happening—don't end up cluttering your results with a bunch of "FALSE" errors, I suppose you could just tweak the IF functions. If you add an empty string ( "" ) right after the day name, the formula should look something like this:
=IF(C35=1;"Mon;";"")&IF(E35=1;"Tue;";"")&IF(G35=1; "Wed;";"")&IF(I35=1;"Thu;";"")&IF(K35=1;"Fri;";"") &IF(M35=1;"Sat;";"")&IF(O35=1;"Sun";"")

p.s. you might want to adjust the cell references to fit whatever specific setup you have going on, but the rest of the heavy lifting should be handled
just like el.zec suggested earlier.

Walter Jackson48 said:Try setting your ranges like this: D1:D1000, E1:E1000, and F1:F1000 (rather than just using the whole columns like D:D, E:E, or F:F) and then just drag the formula down to row 1000. At least, that’s if I’m following you correctly.

Not quite... but you helped me out. My laptop screen finally feels bright again...
this formula actually works
=IF(C1="K7";D:D;IF(C1="K8";E:E;IF(C1="K9";F:F;"")) )

This formula works fine
=IF(F1=1;"/";IF(F1=2;"//";""))

But this one keeps throwing an error
=IF(F1=/;"1";IF(F1=//;"2";""))

Does it just need a quick fix, or am I asking for too much?
Or... is there a better way to get the same result?

Does anyone know how to skip cells in Excel? For example, I want to jump from cell A1 directly to E20 by pressing Shift or Enter. It would work like those Chase Bank tax forms you download online.

Well, if that’s what you were getting at... maybe just type E20 directly into the Name Box—you know, that little field up in the top left corner next to the formula bar—and hit Enter. That should zip you right over to cell E20. If that's what you meant, anyway!

restlessheron19 said:this formula works fine
=IF(F1=1;"/";IF(F1=2;"//";""))

but this one keeps throwing an error
=IF(F1=/;"1";IF(F1=//;"2";""))

Do I need to tweak it a bit, or is this just a lost cause?
Or... is there some other way to get the same result?

Look, I honestly have no clue what you're actually trying to achieve here. I'm assuming you were messaging someone privately and decided to post the question here instead (unless I somehow missed your previous thread, which happens, I guess)

No, that’s not what I meant at all. Here’s the scenario: I enter the customer info in cell A6, but then I need to jump straight to G16 to input the invoice number. Right now, if I hit Enter or try to Shift my way over, I’m forced to click through every single row from A7 down to A16, or drag across B16, C16, and so on. There has to be a way to jump directly from A6 to G16 with just one keystroke. Download a standard IRS tax form and you'll see exactly what I'm talking about.

Walter Jackson48 said:I’m not quite sure what you’re trying to achieve with this formula. I assume you were messaging someone privately and now you're replying here (unless I somehow missed your previous post)

=IF(F1="/";"1";IF(F1="//";"2";""))

Here is my goal with this formula:
If I type "/" or "//" into cell F1, I want cell K1 to display either a 1 or a 2.

The problem is, if I put the "/" inside quotation marks... I can't actually type the "/" character into the cell itself anymore.
I could technically enter it into the formula bar, but that isn't a practical solution for me.

To prove what I mean... try this... but read this part first.

1. Type a single "/" into a cell. Do this before you enter the aforementioned formula anywhere in Excel.
The character appears fine. Everything works.

2. But once the formula is entered and cell F1 is active—in this specific case—this happens:
When I try to type the "/" again, even without a formula present, the keystroke triggers an Excel shortcut... specifically, it opens the "File" menu. And the worst part? That behavior sticks. I have no idea how to disable it.

So, I have two questions...
1. Regarding the formula itself.
2. How do I stop typing Shift+7 or just "/" from triggering the "File" button via keyboard shortcuts?
Basically,the standard way of typing "/" in Excel just doesn't work for me anymore.

lonebear14 said:Noooo, that’s not what I meant. Seriously? Here's an example: In my Excel sheet, I input customer data in cell A6, but then I need to jump straight to G16 to enter the invoice number. Right now, if I hit Enter or Tab from A6, I’m stuck tabbing through A7, A8, A9... all the way to A16, then shifting through B16, C16... until I finally hit G16. Is there a way to just leap from A6 to G16 with one keystroke? Just one? Download a standard IRS tax form and you'll see what I'm talking about.

The easiest fix? Lock the cells you want to skip, protect the worksheet, and set it so only unlocked cells can be selected.

restlessheron19 said:So, I have two questions...
1. The formula itself.
2. How do I stop this from happening? When I hit Shift+7 or even just "/", the keyboard triggers the "File" menu instead of typing the character.
Basically...I can't even type a simple slash in Excel using the standard method.

The formulas look solid. As for the slash issue—just double-click cell F1 to enter edit mode. That should let you type whatever you need.

brightcyclist2 said:Your Formula 1 logic is solid, but when you're typing a "/" or "//", you have to double-click cell F1 first just to let the system accept the entry.

Thanks... helpful tip...

Since I'm constantly entering things like "/"... I was messing around in Excel and stumbled upon this...
Tools > Options > Advanced > Under "Editing options" - Microsoft Office Excel menu key: /
I just cleared out the "/" from that field, saved it, and now there's no need for the double-click.

Public Sub DeleteRowOnCell()

ERROR Resume Next Selection
Selection.SpecialCells(xlCellTypeBlanks).EntireRow .Delete
Delete ActiveSheet.UsedRange

End Sub

I tried using this macro to handle some data sorting... could use a hand here...
Here’s the scenario:
1. Select column H.
2. Hit the "sort" button.
3. In column H, the blank cells are highlighted in red.
4. This macro is supposed to wipe out those empty cells within the selection—meaning it should delete the entire row.

I'm trying to achieve a specific result, but I've hit a bit of a wall.
If you put an "X" in cells F1 and F16... say you've selected a range of 15 + 15 numbers...
Try deleting the "X" in cell E16 just to see how it behaves.

If you have the first 15 numbers selected initially—so one "X" in F1 is enough...
Then select column C and click the "sort" button.
The colored rows get deleted... but what I actually need is for the numbers in cells D16 through D30 to be wiped out too. Since the cells in column C from 16 to 30 are blank, the whole rows should be going.
Is the command failing because there are formulas in the cells? Or something else?
If I copy column C to a new column—let's say column M—and use "Paste Special" > "Values," then column M won't have any formulas. The macro *should* work then, but it doesn't.
I've tried different formulas, but the result is always the same.

Here is an example: http://www.megashare.com/3858054