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

Sophia Newman6 · 2009-10-13T20:10:30+00:00

Can someone please help me out here? I need to number pages in Word, but I want the numbering to actually start on the third page. Does anyone know how to pull…

restlessheron19 Active Member

69 messages
joined Mar 2011

#841 · Jun 11, 2011

Nicholas King said:=IF(Sheet1!A1="", "", Sheet1!A1)

If you want, back in the 2007 version, you can just go into the Advanced Options and toggle off the setting to show all zeros in cells. If you do that, you won't even need this IF statement.

Thanks so much! 🙂

Joseph Chavez7 Newcomer

3 messages
joined Aug 2006

#842 · Jun 11, 2011

Nicholas King said:Give this a shot:

=IF(A1>B1;MIN(4/3;B1+1)-MAX(1,125;A1);MAX(0;MIN(1/3;B1)-MAX(0,125;A1)))

A1 represents the start time, while B1 is the end time (entered as, say, 9:00 PM).

The formula doesn't quite hold up when you feed it extreme edge cases—like a shift running from 5:00 AM until 4:00 AM the next morning.

Thanks a million! 🙂

Kyle Johnson2 said:You aren't being clear enough...

Look, the logic is simply calculating how many hours fall within the 3:00 AM to 8:00 AM window during a specific shift; for instance, if the shift is 9:00 PM to 11:00 AM, you get 5 hours, but if it’s 5:00 AM to 3:00 PM, you only get 3 hours.

Anyway, I went through the tutorial and everything is explained perfectly, so there's really no issue here. Thanks to you as well! 🙂

Best!

Daniel Bennett76 Newcomer

4 messages
joined Aug 2006

#843 · Jun 11, 2011

Can anyone help me out here?
I'm trying to figure out how to merge multiple Excel files into one single workbook, where the data from each subsequent file just gets appended directly underneath the previous one.
Hopefully, that makes sense.

Nicholas King Regular

716 messages
joined Jan 2023

#844 · Jun 11, 2011

Ashley Collins36 said:I’m looking for some help if anyone happens to know the answer.
Is there a way to merge multiple Excel files into one single workbook—specifically, where the data from each subsequent file just gets appended directly underneath the previous one?
I think I explained that clearly enough, hopefully.

Is there some sort of massive, underlying reason why you wouldn't just use the standard copy and paste method? 😕

Daniel Bennett76 Newcomer

4 messages
joined Aug 2006

#845 · Jun 11, 2011

Aha. I need to merge about 500 to 600 documents into one single file. We're talking roughly 150 docs every month.
Do you actually have a suggestion, or are you just curious?

Nicholas King Regular

716 messages
joined Jan 2023

#846 · Jun 12, 2011

Ashley Collins36 said:Aha. So, there are about 500-600 documents that need to be merged into one single file. Roughly 150 documents per month.
Do you actually have a suggestion, or are you just being nosy?

Look, 500 to 600 documents isn't exactly "a large volume." I certainly hope you aren't describing your symptoms to your doctor with that same level of "detail and precision."

Maybe save the passive-aggression and the sarcasm—especially in a situation where you're actually asking for help—for someone else. I’ve never been one to go looking for praise or a thank-you note, but I do think I have the right to expect at least a modicum of politeness from people who are supposedly seeking assistance.

Goodbye.

Daniel Bennett76 Newcomer

4 messages
joined Aug 2006

#847 · Jun 12, 2011

😕

If anyone actually knows how to fix this and is willing to lend a hand, I’d really appreciate it.

Kyle Johnson2 Newcomer

1 message
joined Feb 2015

#848 · Jun 12, 2011

Ashley Collins36 said:How do I merge multiple documents in Excel so that the content from each one just stacks right under the previous one?
Hope that makes sense.

If anyone knows how to do this and wants to help, I'd be super grateful.

Gonna jump on the bandwagon and agree with @Lord Byron here.

1. Yeah, you didn't really explain it well.
2. Using the word "document" usually implies Word. In Excel, we're talking about a "Workbook" which holds several Sheets.
3. Assuming you just had a slip of the tongue since you mentioned Excel later.
4. Here’s why the explanation is fuzzy: you haven't said if you need to pull data from "a few" files or "500" different Workbooks that aren't even open, just sitting in the same folder as your SUMMARY.xls Workbook.

Also, since a Workbook has multiple Sheets, you haven't specified how the data is laid out. Are the tables formatted identically? Do you need to grab data from just Sheet1, or also Sheet2, Sheet3, etc.?
And what about the names of the Sheets in those Workbooks—are they identical to the ones you're pulling from? And so on and so forth...
5. With such a vague request, you should've attached a few sample Workbooks to the post and used dummy data to show exactly where everything needs to go.

btw: Check these links for some VBA macros to handle Mergedat
- http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
- http://www.rondebruin.nl/fso.htm

Nicholas King Regular

716 messages
joined Jan 2023

#849 · Jun 12, 2011

Ashley Collins36 said:If anyone actually knows how to handle this and is willing to lend a hand, I would be incredibly grateful.

http://www.rondebruin.nl/merge.htm

Daniel Bennett76 Newcomer

4 messages
joined Aug 2006

#850 · Jun 12, 2011

1. I need to copy data from 654 different Workbooks into a new SUMMARY.xls Workbook. None of them are open, and they’re all sitting in the same folder as the SUMMARY.xls
2. I only need the data from Sheet 2. All the sheet names are identical.
3. The table layouts are pretty much the same across all files.

Since you guys shut me down right out of the gate, I'm actually kind of embarrassed to even attach my "work" here. 😁

I'll try to figure it out using the links you provided. Thanks a lot!

Nicholas King Regular

716 messages
joined Jan 2023

#851 · Jun 12, 2011

Ashley Collins36 said:Well, you all just completely cut me down at the starting line—honestly, now I’m actually a bit hesitant, maybe even a little embarrassed, to even bother attaching my "work" here. 😁

It seems like nobody actually bothered to cut anything—not even a single thing. I suppose you’re looking for me—or perhaps you’re just testing the waters to see if I’m actually paying attention here. If there’s something specific you need addressed, or if you have some grievance you’d like to air, go ahead. I’m listening, though I can’t promise I won't find your reasoning... lacking.If you take a moment to actually scroll through this thread, you’ll see that nobody ever gets shut down here—no matter how basic or elementary their question might seem. Back when I was first learning the ropes, there were always people willing to lend a hand, and I suppose that creates this sense of obligation—this feeling that you owe it to the next generation to pay that kindness forward. But, honestly, you’ll eventually come to agree with me... The method. I mean, if I’m being completely honest—and I suppose I should be—that performance you gave could have been significantly better. It wasn't exactly a disaster, but there was definitely room for improvement.

I honestly think that attaching raw, actual data is probably not the smartest move—unless, of course, someone can actually prove that there is absolutely no other way to get the job done. 🙂

I suppose I'll attempt to navigate my way through using those links you provided—hopefully, they actually lead somewhere useful. Much appreciated!

From what I can gather—and I might be wrong here—the link I shared seems to do exactly what you’re looking for. It features a pretty intuitive dialogue box for configuring all the settings, which makes things much easier. You should be able to select the specific Excel sheet you want to pull data from, determine the connection method, and handle all those other little details without much fuss.

Daniel Bennett76 Newcomer

4 messages
joined Aug 2006

#852 · Jun 12, 2011

Nicholas King said:Nobody is attacking you. If you look through this thread, you'll see nobody ever gets attacked here, no matter how basic their question might be (back when I was learning this stuff, people were always willing to help me out, so I guess you just feel an obligation to pay it forward to those learning now). But, I agree, the way you handled yourself could have been better.

I don't think attaching actual data is the best move unless there's absolutely no other way to show the problem. 🙂

From what I can see, the link I posted does exactly what you need, and the dialog box for setting things up is pretty intuitive. You can pick which sheet to pull from, the merge method, all that stuff.

I used your link to get it done. Thanks!!! 🙏

restlessheron19 Active Member

69 messages
joined Mar 2011

#853 · Jun 12, 2011

I’m working with a Sheets file that relies on several different Macros.
Everything is running exactly how I want it to. Well, except for one tiny detail. Or maybe two..
Once the processing is done, the Sheets file is ready to be converted to a PDF.
One of the Macros I use handles sorting, which involves deleting entire rows.
I have a specific Print area set up. Since those rows get deleted, the Print area shrinks accordingly, which is actually fine by me.
So... all that's left is... the intended content... within a specific number of rows...
The sorting and the Print area effectively dictate how many rows remain..
Here is what I am looking to do.
Use a Macro to automatically re-number... the remaining rows.
For example, let's say the numbering starts in cell A5 and should stop at the second-to-last row... as defined by the Print area.

Side note:
I found a workaround..
After the sorting finishes, I just trigger a command to number everything down to the very end of the document..
Since the Print area is flexible... any numbers falling outside that zone simply don't print..

brightcyclist2 Member

15 messages
joined Mar 2023

#854 · Jun 12, 2011

restlessheron19 said:Problem solved..
After sorting, I just triggered the numbering command.. it runs all the way to the end of the document..
Since the print area is flexible... any numbers falling outside that range won't show up on the printout..

Why not just bake that numbering command directly into your existing macro? That way you don't have to trigger it "manually" every single time.

rapidhawk23 Newcomer

8 messages
joined Jul 2019

#855 · Jun 13, 2011

driftingnomad68 said:Formula:
=IF(COUNTIF(A1:A5,1)>0,1,AVERAGE(A1:A5))

If A1:A5 is your range of grades, just change 🙂

Thanks, brightcyclist2, I managed to get that working! But now I need to take a final grade with two decimals and format it as a whole number so I can get a final score of 1, 2, 3, 4, or 5. I'm doing this via cell formatting. However, when I try to run statistics on those numbers—like counting how many students passed with a 2, 3, 4, or 5 versus failing (1)—it won't work. It seems like COUNTIF ignores cells that have been visually formatted. What should I do???

Nicholas King Regular

716 messages
joined Jan 2023

#856 · Jun 13, 2011

rapidhawk23 said:Thanks, Sister, I actually managed to pull that off. But now—here's the catch—I need to take a final grade from two decimal places and turn it into a whole number so I can get a final score of 1, 2, 3, 4, or 5. Right now, I’m just using cell formatting to change how it looks. However, when I try to run some statistics—you know, like counting how many students passed with a 2, 3, 4, or 5, or how many failed (1)—it just isn't working. It seems like COUNTIF won't recognize cells that have just been formatted. What am I supposed to do here???

Don't just mess with the formatting; you should probably just use =ROUND(argument).

Taylor Brown Newcomer

7 messages
joined Dec 2010

#857 · Jun 14, 2011

Why on earth is Word 2010 skipping lines when I hit "enter"? It feels like it’s jumping an entire line every single time... how do I actually fix this setting? Everything looks totally messed up on my end here...

driftingnomad68 Member

18 messages
joined Sep 2005

#858 · Jun 14, 2011

It’s because it assumes you want to start a whole new paragraph every single time you hit Enter, instead of just letting the text wrap naturally like it should.

So, if you look up in your settings, go to the Home tab, then hit that Paragraph sub-tab... you'll see this little icon with those up and down arrows next to some horizontal lines. It’s labeled Line and Paragraph Spacing. Just click that, and then select Remove Space after Paragraph. That should do the trick!

Sarah Turner98 Member

15 messages
joined Jul 2010

#859 · Jun 16, 2011

Hey there,
I’ve been using Google Sheets quite a bit, and while I can deal with most of its quirks, I’m totally stumped on this one. Not even sure if it’s possible, but figured I'd ask you guys🙂

So, here’s the deal...

On Sheet 1, I have
names and weights (let's say there are 4 people entered)

On Sheet 2, I have team names and a total weight (say, 2 people per team, and their weights get added up)...

And when I put a formula on Sheet 2 to pull the weights from Sheet 1 and sum them up, everything works perfectly fine...

But, the second I sort Sheet 1 by weight...
Like, largest to smallest? Suddenly, the weights on Sheet 2 start shifting around. Since the formula is tied to specific cells in Google Sheets rather than the actual values, it breaks. Is there any way to sort Sheet 1 without messing up Sheet 2? Basically, how do I lock onto a specific value so that even if its position changes, the formula follows it?

🙂

Michael Jackson10 Active Member

140 messages
joined Feb 2023

#860 · Jun 16, 2011

First things first: head over to the first sheet and toss in a new column. You'll need to assign a team name to every person there, since that’s going to be super important for your math later on. Once that's done, don't just go around adding up cells using absolute references on the second sheet. That's a rookie move! Instead, use a conditional subtotal based on that team attribute. It's the only way to make sure your calculations stay dynamic, no matter how you shuffle the people around on that first sheet...

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

🔗 Similar threads