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

Benjamin Wilson7 said:Just to give you a quick rundown—this issue can be handled by either of these two formulas (though honestly, just the second one might suffice):

With this particular workaround, you’re essentially copying over the input data, except you aren't outputting anything where the criteria don't match. So, I guess you end up with a whole lot of empty rows.

BTW, the formula itself doesn't actually output "every number"—it really just pulls through A1 and B1, if anything at all.

So, you just drag the formula down. I didn't realize those empty rows were throwing a wrench in the works!

Plus, didn't he mention that everything gets rewritten from row 14 to 26? So if he’s looking for values between 23 and 28, it makes perfect sense that the other rows stay blank.
I mean, what else is a formula supposed to do besides pull existing data? It's all about finding the right way to make that happen.

This second option totally fixes his issue. From here, we could even play around with some variations—like, say, having the target elements (23, 28) typed into a different cell so he doesn't have to mess with the formula itself every single time...

One step at a time! I have a wonderful feeling about this, truly. I am going to dive in and give it a proper go, and once I see how it performs, I will be sure to circle back and update everyone on my findings.

Unfortunately, it just isn't working quite right; I keep getting an empty cell,
and even when I attempt to structure it this way: =IF(AND(A1>=23,A1 "false" instead of the result I'm looking for...
I will go ahead and post an example of exactly what I am trying to achieve right now.

Gregory Rogers20 said:Ugh, nope. It’s just not working. I keep getting an empty cell,
or when I try this: =IF(AND(A1>=23,A1 "false" at me...

- Honestly, it works perfectly fine for me, and it should work for you too! You probably just messed up the cell reference—it's not A1, it's B1. Also, did you see where I put the word false inside quotation marks?
=IF(AND(A1>=23;B1);"") - just try copying this into cell B14 and dragging it down. You'll get letters as results or just blank cells.

- That first formula is meant for cell A14 if you actually need the numbers, but looking at your screenshot, you don't seem to need them since you've got those "f" letters sitting in columns A and B.

- See the difference? You can't use a comma; you have to use a semicolon, and don't put anything inside the quotation marks.
It shows letters only for values between 23 and 28. For everything else, it stays blank because of the empty quotes ("" means nothing, aka a blank cell). If you take the quotes out, it'll just say FALSE, which basically means the formula's conditions weren't met. If they are met, you'll see f, g, h, and so on.

To make it simple: just copy both formulas into cells A14 and B14, drag them down, and then you can decide which one actually does what you want.

Hold on just a second, I suspect you might be working with a completely different version of Excel where semicolons are the standard delimiter! For my part, the system simply refuses to accept them; it will only process the data correctly if I use a comma instead!

To clarify once more, here is precisely what I am trying to achieve:

That’s just how the American version of Excel works on your machine—according to the manuals, they don't use semicolons. But hey, if that's causing a headache, just swap them out for commas where needed; just don't go adding anything extra.

It’s working perfectly now that I’ve pulled it through! You really should have given me a heads-up earlier 😁 though,
is there any way to make it print everything from the very beginning without skipping rows? (I'm talking about those empty little boxes), so that they all look neatly filled in—do you think that's possible?

Gregory Rogers20 said:It’s working now since I pulled it back! You should've told me sooner 😁 but,
can't we just have it print everything from the start without skipping lines? (I mean those empty checkboxes), like, can they all just be filled in nicely? Is that possible?

Well, that's a whole different story.
The easiest way would be to just drop in a macro that wipes out the blank rows. You can grab that code from Ivanac—just go back and read his instructions, he covered it a few pages ago.

I'm not saying that's the *only* fix, but we really need to put some thought into this and actually answer the previous questions. It's crucial to know how much the table being analyzed changes or stays stable, you know? I don't want to keep repeating myself...

Well, if you ask me, I would definitely prefer to have a bit more data to work with—say, at least 100 rows! Otherwise, those pesky "empty rows" just end up cluttering everything up, making the whole sheet look messy and disorganized instead of being neatly arranged and professional...

And what do you think about an alternative approach, perhaps where all that information is displayed within a single cell, with each entry stacked vertically one beneath the other?

I’ll try to brainstorm some ideas tomorrow afternoon, but I'm currently swamped getting things ready for tomorrow morning, so I just don't have the bandwidth right now.

Let me jot this down quickly while I can—my head is spinning a bit today and I’m feeling a little off, so let’s see if this makes sense to you:
Basically, just list the numbers you're looking for in column D1 and downwards. For instance, using your previous example, you'd put: 23, 24, 25, 26, 27, 28.

Pop this formula into cell B14 and drag it down the column.
=VLOOKUP(D1;$A$1:$B$13;2)

Drag it down one more time just to be safe. Once you start seeing that #N/A error, you know you're finished!
One huge thing to remember: your lookup table absolutely has to be sorted in ascending order. If it isn't, VLOOKUP is going to give you totally wrong results.

You won't have any empty rows now, but you do need to enter the specific numbers you want to search for in cells D1 through D6. You can always hide that column later if you don't want it cluttering things up.

If you actually want the formula to display the numbers themselves, put =VLOOKUP(D1;$A$1:$B$13;1) into cell A14 instead. Just drag that down until the #N/A pops up too.

Assumptions:

- Your data range covers A1 through B13;
- Cell D1 holds your lower limit;
- Cell E1 holds your upper limit;

- You want the results to start printing at A14

Cell A14: =VLOOKUP($D$1;$A$1:$B$13;2;FALSE)
Cell A15: =IF($D$1+ROW()-14>$E$1;"";VLOOKUP($D$1+ROW()-14;$A$1:$B$13;2;FALSE))

The parts highlighted in red are the ones you—well, you absolutely have to change depending on where your actual data is located in your spreadsheet.

Just grab cell A15 and drag it down as far as you need it to go, and that should be about it.

Quick question for the Excel experts out there 🙂. I wouldn't call myself a total novice, but I haven't spent much time messing around with complex functions. Here’s the situation. I have a workbook with 13 sheets—one for each month, plus a sheet called naknade. In the naknade sheet, I have "name of thing" and "Price." In the "January" sheet, the columns are "First Name," "Last Name," "Thing," "Thing1," and "Thing2." Here is what I need: if someone selects "Lamb" from a dropdown menu, I want it to pull the price for lamb and put it in the "Total" field. Furthermore, if "lamb" is also selected in "Thing1" on that same row, I want it to add that price to the first one. I've tried to be as specific as possible, and I've attached the table below. If anyone knows the simplest way to handle this, I'll buy you a beer 🙂. Here is the link to the test workbook http://rapidshare.com/files/456907421/Test.xlsx

So, how do I delete page numbers from specific pages in Word without them vanishing everywhere else?

Here's the deal: I’ve got this 100-page document, but there are about 10 pages where I have a full-bleed image covering the entire page. When I turn on page numbering, the number pops up right over my image, which messes with the layout and shrinks the picture because of the margin. The annoying part is that every time I try to kill the number on one of those pages, it just nukes the numbers from the whole damn document.
Thanks!

Ethan Palmer69 said:Is there any way to delete a page number from specific pages in Microsoft Office Word without it automatically nuking the numbers on every single page?

Honestly, just set the image layout to "in front of text"—then you can stretch it out however you want—and basically just hide the number behind it.

It’s probably the easiest workaround available.

Benjamin Wilson7 said:I’m rushing this out a little bit—my head is spinning today and I'm feeling quite under the weather—but please take a look and see if this approach works for you:
So, for instance, go ahead and enter the specific numbers you need in column D1 and then drag them down: let's say 23, 24, 25, 26, 27, 28 (based on that example you provided earlier).

Input this formula into cell B14 and drag it down the column:
=VLOOKUP(D1;$A$1:$B$13;2)

And just to be absolutely certain, pull that formula down further; once you start seeing that #N/A error, you’ll know you’ve reached the end of the list.
It is incredibly vital that the table you are searching through is sorted in ascending order, because otherwise, the VLOOKUP function will provide completely incorrect results!

You won't have any empty rows left over, though you will need to input which numbers you are looking for in cells D1 through D6; you can always hide those columns afterward if you want a cleaner look.

If you actually want to display the numbers themselves, put =VLOOKUP(D1;$A$1:$B$13;1) into cell A14 (again, just drag it down until you hit that #N/A error).

Alright, now let me get back to my main point; is it possible to define my numbers using a range, such as "from X to Y"? You see, what I am really trying to manage is a table containing player handicaps—essentially, every player has their own handicap value—and the tricky part is that these values are decimals (like 20.5).
The goal is to categorize everyone into Groups A, B, C, and D. Players within a certain handicap range fall into Group A, while players with higher handicaps move into Group B, and so on...
Essentially, whenever a player's handicap changes, the table should update automatically, and if they drop below a certain threshold, they should automatically shift into a different group.🙂

Now, I realize that might be a bit much detail, but...
Is it possible to set up that "from-to" range logic? 🙂

Gregory Rogers20 said:the issue is also that these numbers are decimals (like 20.5).

Look, what you're doing here is bordering on outright disrespect.

You showed up with one description of your problem—which was actually solved in several different ways—and then, in every single follow-up post, you tack on some new "the issue is also..." which essentially sabotages every solution we've offered (mine included, though how was I supposed to know those numbers could be decimals or wouldn't just increase by increments of 1?).

Do you honestly believe people have nothing better to do than constantly—and for free—re-solve new variables in your problem? It’s a bit like walking up to a crew of contractors while they're pouring a foundation for a single-story house and saying, "Oh, the issue is also that I actually need three bedrooms instead of two." I mean, the contractors will fix it for you—but they’ll certainly send you a massive bill for the extra work. 😁

Is it really that difficult to just sit down and describe the entire situation from start to finish right at the beginning?

You're being unprofessional, and frankly, you're wasting people's time and goodwill. Goodbye.

Ethan Palmer69 said:How do I delete page numbers on specific pages in Word without them disappearing everywhere else?

Use sections. Check out Numeriranje in sections

Nicholas King said:Just set the image layout to "in front of text" and you can stretch it out as much as you want—basically just use it to hide the number.

That’s easily the easiest way to handle it.

Oh, awesome! So how do I actually get rid of numbers 3 and 4 since they're showing up in my Table of Contents and on a blank page?
(Basically, how do I delete the numbers without totally wrecking the rest of the formatting?)

Or what if I need the numbering to start at 2 on, say, the 6th page?