Posts by Kyle Johnson2

Robin Hernandez said:I'm hunting for some Excel tutorials, but I'm struggling. Total newbie here.

Check this link for some tips on the Excel function. Also, if you need to set up dependent drop-down lists, there's a guide for that on the same link too.

Gerald Young25 said:Tried it. Still nothing...

Didn't think you were such a stickler for the rules.
What does the file actually look like? Throw up an example or something. Is that "2d 2h 15min" result coming from a formula or is it just plain text?
Check this link again—I tweaked it based on your example.

Gerald Young25 said:Man, if I could just turn this into actual work hours... how do I convert 50:15?

Try setting the cell format to custom or Time 37:33:55 (check out LINK)

Angela Anderson44 said:Is there any way to auto-fill serial numbers in a column?

Check this out Auto-fill data

Benjamin Wilson7 said:The catch is that other people need to be able to run the Macro, but they don't know my password. (I don't give it out to anyone—total paranoia on my part).

Like Nicholas King said, just lock down the VBE access and "nobody" is gonna see your password anyway.

Benjamin Wilson7 said:The sheet needs to be password protected (except for the data entry cells), but if I do that, I can't run the Macro I assigned to my Button used to refresh the Pivot report.

Basically, you just gotta unprotect the sheet right before the Macro runs the Pivot refresh, then lock it back up once it's done.
Some guy suggested this:
Code:

Elizabeth Fowler46 said:I know, but I was hoping for something like: "Delete all images" 😁
Clicking through every single one is just way too much work.

Besides that Macro Nicholas King shared, you could always just copy the whole thing into Notepad and then paste it back into Microsoft Word (assuming you don't care about the formatting or layout).

Joseph Thompson10 said:Got handed this massive Excel sheet at work with like 18,000 rows and I need to scrub out anything that shows up two or more times...

Adding to what Walter Jackson48 suggested, try running this formula too:
=IF(COUNTIF($A$2:A2;A2)>1;"DUPLICATE";"OK")
If you’re dealing with duplicate data spread across multiple columns, check out this fix HERE

Nicholas King said:Duh, you obviously have to tweak the formula for whichever cell you're dropping it into.
For A3, just use =COUNTBLANK($A$2)=0, and keep going from there.

You got a point there, @Nicholas King/">@@Nicholas King, but I was just trying to save him the headache of doing it cell by cell instead of letting him just grab the whole range.🙂
Later.

cosmicbison19 said:you gotta enter stuff into the cells in a specific order.

Say you're working with A1:A10
A1: no Data Validation applied
A2: =COUNTBLANK($A$1)=0
grab the range then hit Data Validation => Custom
A3:A10 =COUNTBLANK($A$1:$A2)=0

James Rogers53 said:I've got an Excel file with over 80 sheets and I need to dump them all into one single sheet.
Is there any way to do this without just grinding through copy-paste?
Luckily, the number of columns is the same on all of them.

Check these links out

- Condensing multiple Worksheets Into One - Copying
- Copying a Range from Multiple Worksheets

gentleranger82 said:So, here’s the vision for this Invoice setup: I want to type in a client's name and have the whole block—address, zip code, phone number, city—just pop right in there automatically. I also want the invoice number and today's date to auto-fill the second the data entry form opens up. Then, once I start punching in items, the unit price and the unit of measure should just show up on their own. I'll toss in the quantity, get my subtotal for that item, and then have the grand total waiting for me at the bottom of the form. Done.

That link @Joshua Garcia3/">@@Joshua Garcia3 sent you isn't gonna do much for you anyway. I haven't even finished that tutorial—and honestly? Probably won't.
So, check out this link. Invoice (Just rename him to Publisher). This form does exactly what you said.

Rebecca Wright63 said:Basically, I know how to do page numbering BUT
I'm wondering if there's any way to skip the first (first two) pages
so the numbering actually kicks in on page 3 or whatever?
:

Or more specifically, check out THIS post #703

Quote : Nicholas Chavez70

Nicholas Chavez70 said:So, I’ve got about 97 pages of this massive thesis sitting in front of me, and now it’s acting up. How do I get the page numbering to actually start at page 7?

So you finally finished that thesis? Nice work. Only problem is, they clearly didn't teach you how to handle Page Numbers in Word at your university. 😉

If you actually bothered to look at this thread, you’d see post #703. Check the links there—they lay everything out step-by-step. Plus, Google isn't exactly ignoring those links, either.

(Damn. Go ahead and make it in any language besides English.

🤣 Man, you really got him worked up. And looking at that count under his handle—over 8,000 posts? Seriously? I’m surprised a moderator hasn't stepped in yet. 😉

Nicholas Chavez70 said:Some idiot at Microsoft couldn't be bothered to make a simple window where you just pick the starting page for numbering. Like, seriously? "Which page do you want to start on?" That's all it should be. But nah, that’s Microshit for you—they'd rather make us jump through hoops just to mess with us.

That's a total caricature. It’s like being pissed off at car manufacturers because they didn't build a vehicle where you just say, "Hey, take me here and there," instead of actually having to drive the damn thing yourself. 🙂

Just take it easy. One step at a time and you'll get those page numbers sorted—and hopefully nail that graduation too. 😉

Arthur Ramirez20 said:My local soccer club put me in charge of tracking the league stats... just the basics, standings, matchups, scores... I'm wondering if there's a specific program for tracking all that, or if I should just stick to Excel? I tried searching online but didn't find much.

Maybe try building something similar to this for the NFL.

Michael Jackson10 said:but if you ask me, about 70% of people don't even get the difference between absolute and relative references, let alone what happens when you start copying them,

Fine, we'll help 'em out. 😉
With relative references, the range shifts when you copy it, but with absolute ones, the range stays put. By "range," I mean the specific cell or span of cells you're working with.
If you want to turn a relative address into an absolute one, just toss in a $
sign.
Check this link if you actually care about ABSOLUTE vs. RELATIVE cell references in Excel

Bryan Evans77 said:how do I make it so stuff on the first page doesn't mess with the second? Like, I want to lock it to a specific cell, but if I move that cell, I need the formulas to follow along too.

Just use a formula like this (and yeah, you gotta use absolute references for the range)
Copy the formula down

=SUMIF(Sheet1!$B$2:$B$5;Sheet2!A2;Sheet1!$C$2:$C$5 )
or
=SUMIF(Sheet1!$B$2:$B$5;A2;Sheet1!$C$2:$C$5)



Or just use a Pivot Table

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

Joseph Chavez7 said:Hope that makes sense..

Not really.
First you mention 9:00 PM to 11:00 AM, then 3:00 AM to 8:00 AM, then some "set criteria" without actually saying what the criteria are.

If you need formulas for subtracting time check out this tutorial.

For your specific example, if you want a formula for A1 and B1 (start-end) that calculates the duration regardless of whether it's day or night, use this:
Code:

e.g., START-END
03:00-08:00 will give you 5:00 hours
21:00-11:00 will give you 14:00 hours (handles crossing midnight)

If one of the cells is empty, the result stays blank (assuming that's what you meant by "criteria").

Larry Ward8 said:I’d want to pull in all my customers by name (business name), and when I click them, have it open up the specific company info needed for an INVOICE so I don't have to hunt down tax IDs and details every single time.

And if possible, let me filter by customer to see how many invoices they've had. Basically just a tiny accounting program.

On top of what @Nicholas King/">@@Nicholas King gave you to chew on, I’ll just add

The FIRST PART of the question:
Search Google for "Hyperlink in Excel to a Sheet" (hyperlinks)
Basically, on one Sheet labeled "HYPERLINKS," you have a list of all companies, and you'll have that many Sheets. Click the hyperlink, and boom—it automatically jumps you to that specific company's Sheet and their data.

The SECOND PART:
On a single "COMPANIES" Sheet, you have a table pulling data from all the other Sheets (companies).

The key is planning out all your parameters and how the data and Sheets are organized. That's if you're tackling this project yourself.
BTW: Check out how a "little project" can be designed for an Invoice—there's even a sample download there.

But look, if you're a total Excel newbie, don't get it, and you aren't exactly a wizard with formulas, just listen to @Nicholas King/">@@Nicholas King.