Microsoft Office Q&A: Ask anything (Word, Excel, PowerPoint, etc.)

Alexander James77 · 2012-03-17T08:48:56+00:00

Walter Jackson48 said: Perhaps this specific formula might prove useful to you: =OFFSET(COLONA;COLUMN()-COLUMN($E$1)+((ROW()-ROW($E$1))*(ROWS(COLONA)/1));0;1;1…

Gerald Barnes Newcomer

3 messages
joined Dec 2012

#501 · Dec 11, 2012

Kyle Johnson2 said:I have no idea why you’ve made this so complicated, but honestly, just go find some tutorials on copying in Excel. See if there's anything you can actually adapt to fix your mess.

- Copying via a double-click on a cell.
- Copying data using the Advanced Filter feature.
- Copying selected cells.
- Copying non-adjacent cells.

It’s most likely that "Double-click to copy" option. I'm having an issue where View Code is grayed out, which is preventing me from moving forward... I don't get it.

Anthony Lewis79 Newcomer

7 messages
joined Apr 2008

#502 · Dec 12, 2012

I've run into a bit of a snag... I just finished installing Windows 8 Office 2007 on my machine. But when I head over to Program Files to look for Office 2007, things seem a little off. Instead of finding winword.exe right there, I'm seeing this subfolder labeled 1033, and it looks completely empty inside... So, does anyone know where winword.exe actually ended up?

Amy Gray52 Newcomer

2 messages
joined Dec 2012

#503 · Dec 12, 2012

Hey there!

Quick question regarding Excel:

Is there a way to select an entire column in an Excel spreadsheet (specifically one containing numbers—in my case, employee seniority data) and have every single value increase by one (or any other amount, really)?

Basically, I’m managing seniority records for employees at a firm, and every month I need to bump everyone's total up by one month. I’m trying to avoid the nightmare of manually updating every single cell one by one. Is there a way to just do this all at once for the whole range?

Thanks!

Walter Jackson48 Active Member

234 messages
joined Feb 2010

#504 · Dec 12, 2012

Take a look at this; it might actually be useful for you:

Kyle Johnson2 Newcomer

1 message
joined Feb 2015

#505 · Dec 12, 2012

Amy Gray52 said:Is there any way to select a column in an Excel sheet (filled with numbers—specifically seniority data) and just bump all those numbers up by one (or whatever amount)?

Try using a VBA macro that you can run manually whenever you need to bump specific cells on a given Sheet by a certain number.
You could even create a Button to trigger the macro. Just a heads-up: buttons are risky because if you misclick, there's no UNDO once a macro runs. But hey, I'll give you a second macro to subtract the same amount. For the subtraction version, just swap out the math operator in this code:
Code:

Sub plus()
EnableCancelKey = xlDisabled Application
ScreenUpdating = False Sheet1
Sheet1.Range("A1") = Sheet1.Range("A1").Value + 1 Sheet1
Sheet1.Range("A2") = Sheet1.Range("A2").Value + 1 Sheet1
Sheet1.Range("A3") = Sheet1.Range("A3").Value + 1 Sheet1
Sheet1.Range("A4") = Sheet1.Range("A4").Value + 1 Sheet1
Sheet1.Range("A5") = Sheet1.Range("A5").Value + 1 Sheet1
Sheet1.Range("A6") = Sheet1.Range("A6").Value + 1 Sheet1
End Sub

To see how this actually works in practice, check out this tutorial

Kyle Johnson2 Newcomer

1 message
joined Feb 2015

#506 · Dec 12, 2012

Gerald Barnes said:So you're saying it's probably just that "double-click to copy" cell option...?

You sure about that? What happens when a user double-clicks some other random cell?
Maybe look into a more advanced VBA Macro to tighten things up and cut down on errors.

Gerald Barnes said:...wait, what?

Same here, honestly. 😉

Jack Hayes Newcomer

7 messages
joined Nov 2008

#507 · Dec 12, 2012

Amy Gray52 said:Hey there!

I have an Excel question:

Is there a way to select a whole column in an Excel spreadsheet (where I have numbers—specifically employee seniority data) and increase all those numbers by one (or any other amount, really)?

Basically, I’m managing seniority records for employees at a company, and every single month I need to bump their seniority up by one month. To save myself from the headache of manually updating every single cell one by one, is there a way to just do it all at once?

Thanks so much!!!

If you have a column showing seniority in months, you should link it to a date—since seniority depends on the date—which could be sitting in a header cell in the first row.
For example:
You could use a hidden column to enter the initial seniority number for each employee (let's say starting at cell A3...). If cell B1 contains your target date, and you want the seniority in months to show up in cells starting from B3, just type this formula into cell B3: =A3+MONTH($B$1) and then drag it down. That way, whenever you update the date in cell B1, the seniority for everyone in column B (from B3 onwards) updates automatically. 👍
So, since you already have a column with the current seniority, just copy it over to a new column, pick a cell for your date, and use that formula to link them together.

Nicholas King Regular

716 messages
joined Jan 2023

#508 · Dec 12, 2012

Jack Hayes said:let's say you have a date in cell B1, and then starting from B3... if you're trying to calculate seniority in months, just put this formula in cell B3: =A3+MONTH($B$1) and then drag it down to the end of your list.

Aside from the fact that you don't actually need to type the date into a cell—you could just call it using TODAY()—this particular formula is going to cause a massive headache once we hit the new year. When the month rolls over from 12 back to 1, it’ll basically strip 11 months of seniority away from every single employee on your sheet. That's obviously not what you want.

Depending on how your HR department specifically defines a "full month" for seniority purposes, you might find that the formula =DATEDIF(A3, TODAY(), "M") works much better.

Jack Hayes Newcomer

7 messages
joined Nov 2008

#509 · Dec 13, 2012

Nicholas King said:Besides the fact that you don't actually need to type the date into the cell—you can just pull it in using the TODAY() function—there's a bigger issue here. Once we hit the new year and the MONTH value flips from 12 back to 1, this formula is going to accidentally strip 11 months off everyone's seniority. It's one of those little logic traps that can really mess things up for the staff.

Depending on exactly how you're calculating those months of service—specifically if you're looking to count only completed full months—you might find that using the formula =DATEDIF(A3, TODAY(), "M") does the trick.

Of course, you really need to set up those opening balances when transitioning into a new year. Though, if you actually build the year into your formula, you might be able to skip that step entirely. Since these reports are usually tied to a specific month anyway, it just makes sense to include the date or the month's name right in the report title. I was also thinking about how you might need to prevent someone's seniority from increasing for a particular month. A simple workaround would be adding an extra column with 1s and 0s to toggle that. Also, a quick heads-up: using the TODAY() function is a total nightmare if you're trying to run a report for a previous month. At the end of the day, automation is great, but it only makes sense up to a certain point.

Amy Gray52 Newcomer

2 messages
joined Dec 2012

#510 · Dec 13, 2012

uff

Thanks for all the input, everyone.

I suppose I’ll have to sit down and actually wrap my head around everything you guys just laid out. 😉

I’d say I’m pretty decent with Excel—I can handle writing out a complex formula if I have to—
but when it comes to using a Macro, I’m basically clueless.

Anyway, thanks again!

Nicholas King Regular

716 messages
joined Jan 2023

#511 · Dec 13, 2012

You don't actually need a Macro—not really—much like how you don't need any of those "turnkey" solutions that leave you completely unable to make even the smallest tweak or upgrade down the road.

Kyle Johnson2 Newcomer

1 message
joined Feb 2015

#512 · Dec 13, 2012

Amy Gray52 said:I know my way around Excel enough to write long formulas
but I don't have a clue about Macros

You were looking for a way to bump up some numbers. So, here's a Macro for you.

If you want to see how to handle seniority calculations, check out the tutorial and grab the attached file. You can use the TODAY()
function in column B.
- seniority calculation example

shadowmoose92 Newcomer

5 messages
joined Jul 2007

#513 · Dec 13, 2012

Kyle Johnson2 said:Let's get started. I suppose one might want to block macro execution. Maybe there's a specific reason for it, though I can't be entirely sure. It seems like a necessary precaution, I guess. In Word.

All right, thanks.
I'll get back to you as soon as I can get to that computer.

Jack Hayes Newcomer

7 messages
joined Nov 2008

#514 · Dec 14, 2012

Amy Gray52 said:Oof

Thanks everyone for all the help

Now I really need to sit down and digest everything you guys just explained 😉

I use Excel enough to know how to write a decent, long formula
but honestly, I have zero clue about using a Macro

Anyway, thanks again!

Look, the easiest way to handle this is to just add a date next to your seniority data. For example, if your info starts in cell A3, put a date in A1. Then, just insert a new column right next to it—Column B—and put a date in B1 too. Think of A1 as your starting point date, and B1 as the specific month and year you want the report for. From B3 downwards, just use a formula to calculate the difference in months between B1 and A1, then add that to the initial seniority from Column A. Use this: =(YEAR($B$1) - YEAR($A$1) )* 12 + MONTH($B$1) - MONTH($A$1) + A3. Make sure Column B is formatted as a number, while cells A1 and B1 are set to date format. You can just hide Column A to keep things clean. It’s super simple, and you can actually see the seniority adjust if you type an earlier date into B1 than what's in A1.

Gerald Barnes Newcomer

3 messages
joined Dec 2012

#515 · Dec 14, 2012

Kyle Johnson2;42635897 said:Are you certain? What happens when a user double-clicks on a different cell?

I am absolutely certain. Both Macros and the VBA editor are greyed out. 🤷
When I double-click, it just opens the cell for standard entry because nothing has been defined to trigger an action. This EXCELLENT option perfectly solves my problem, especially since I can't run anything without Macros or access the VBA enabled. 😠

I also want to take this opportunity to publicly THANK you—both personally and on behalf of ALL OF US who constantly pester you and envy your seemingly endless patience. ☕

shadowmoose92 Newcomer

5 messages
joined Jul 2007

#516 · Dec 14, 2012

Kyle Johnson2 said:Let's get started, I guess. Maybe there’s a way to block a Macro from running. I guess it might be possible, though I'm not entirely certain. Perhaps one could look into the security settings within Microsoft Office to restrict execution. It seems like something that could be managed through the trust center, if that's even allowed. Maybe? In Word.

I did. The security level was set to "high," but I went ahead and bumped it up to "very high."🤷

Anyway, I just caught sight of this—you can right-click on any saved Word document:

Double-clicking just triggers a print command, which is... odd. I mean, you can still open the document if you manually select "open," but that’s hardly intuitive. I guess it's just one of those things. 😬).

Kyle Johnson2 Newcomer

1 message
joined Feb 2015

#517 · Dec 14, 2012

Gerald Barnes;42670055 said:
Kyle Johnson2 said:You sure about that? What happens when someone double-clicks some other cell?

...............
When I double-click, it just lets me type normally because nothing's been set up to tell it otherwise.

And this GREAT option would solve my problem perfectly, but I can't even run it without Macros and "view code."

I think you're only looking at it that way because you haven't actually tried the Macro yet.
Yeah, you're right that double-clicking usually lets you edit a cell, but if you use this Macro, that won't happen anymore. If you had read the tutorial a bit more closely, you’d see that double-clicking any cell on Sheet1 just copies the data to the exact same spot on Sheet2.

There might be some more advanced Macro out there that does a partial copy from source to destination, but I haven't dug into that.

As for why you can't access the VBA... man, I have no clue.
Later.

Kyle Johnson2 Newcomer

1 message
joined Feb 2015

#518 · Dec 14, 2012

shadowmoose92 said:Double-clicking just prints it, obviously. You can actually open the file if you hit "open" instead.

Check your Windows OS and Word settings.

Nicholas King Regular

716 messages
joined Jan 2023

#519 · Dec 14, 2012

shadowmoose92 said:Double-clicking just prints it out, obviously...

"> Start - Control Panel - folder Options - file Types and scroll down to ..doc. What's it say. Is there an advanced or restore button (it says one or the other) on the dialog. If Restore click it then click Advanced (else just click Advanced). **open should be in Bold**, if not select it and press set default."

I suspect this advice is meant for Windows XP.

Walter Jackson48 Active Member

234 messages
joined Feb 2010

#520 · Dec 14, 2012

Amy Gray52 said:Hey there!!!

I've got a question regarding Excel:

Take a look at this if you think it might help, though I suppose
this has already been covered somewhere else:
http://www.datafilehost.com/download-15d47f83.html

Microsoft Office Q&A: Ask anything (Word, Excel, PowerPoint, etc.)

🔗 Similar threads