Posts by cosmicbison19

Kyle Johnson2 said:I suppose I can't say for certain why it isn't working on your end, though everything seems to be functioning perfectly fine with the formula and the filter over here. Perhaps you just need to spend a little more time at your desk getting used to it. 😉

No worries at all, take care.

By the way, looking at that screenshot of yours, try setting the named range to cover D3 through E10, and then set the formula in B3 to =VLOOKUP(A3;gradovi;2;FALSE)

It actually worked... thanks!

Anyway, Benjamin Wilson7, I'm glad I could brighten your day a bit.🙂

Kyle Johnson2 said:Well, I think you probably should have included your actual attempt right here in the thread.

Maybe try selecting the data range from D2 through E10 and give it a specific name called cities.
Then, in cell B2, just pop in the formula =VLOOKUP(A2, cities, 2, FALSE)

It’s just not working... I'm honestly not quite sure why. 😕

The way the formula works is that it looks at whatever is sitting in cell A3,
searches for an exact match within column D (which, in this specific scenario, would be cell D4), and then finally pulls the corresponding value from
column E (so, the text found in E5).

Thanks,

Kyle Johnson2 said:I suppose one might wonder why anyone would bother with the MATCH function when you could just settle for using VLOOKUP instead.

Well, I guess what I’m really asking is, how would the formula actually look for my specific situation?
I’ve been attempting to follow some of the examples on this site, but I can't quite seem to get it to work properly.

Thanks,

Dana Diaz8 said:😕😕🤷🤷🙈

I suppose we could probably manage that using the MATCH formula, 🙂

Hey everyone,

I was wondering if someone might be able to help me out with a specific formula I'm trying to put together:

Basically, I need to match the values in column A against those in column D, and whenever there's a hit, I want column B to pull in whatever text is sitting over in column E.
So, the idea is to have a formula in column B that looks at columns A and D, finds the match, and then grabs the corresponding info from E.

I've attached an image here to show you what I mean.



Thanks so much.

Hey everyone,

I have a bit of a question for the group... so, I've been working in Microsoft Excel, and I was setting up some conditional formatting within a pivot table. For instance, I wanted to make sure any cell where the value equals 1 turns red, while anything greater than 1 turns green, and so on. Everything seems to work perfectly fine at first,

but here’s the kicker: every single time I refresh the table, the conditional formatting just disappears or breaks. It’s incredibly frustrating. If I set the formatting rule to apply to a specific range, say =$E$1:$E$65536, once I hit refresh, the rule somehow splits itself up into weird fragments, like =$E$1:$E$4;$E$104:$E$65536.

It basically creates these disjointed ranges that skip over the actual data area—my table usually sits somewhere between rows 5 and 103, which is where all my actual numbers live.

I honestly can't wrap my head around why this keeps happening. I guess I just don't understand the logic behind why the software would behave this way.

If anyone happens to know a fix for this... I'd really appreciate it. Thanks.

Best,

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.

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

thanks
it seems to be working fine...
I just made a few little tweaks to the setup... basically, I have dates entered into the cells, and when they hit their "expiration date" three years later, I want the cell to change color.
So, next to the cell containing the initial date, I added a calculation that adds those three years—essentially adding 1096 days.

All in all, it’s doing the job.... thanks 🙂
And besides, it's Friday, which always makes things feel a bit easier 🙂

Walter Jackson48 said:Give this a shot:

In a cell like D2, you'd enter the deadline (say, 1/1/2012), then select it, go to Conditional Formatting, choose "Use a formula to determine which cells to format," and in the input box, type =TODAY()=D2. Then, under Format, pick whatever color you prefer and click OK.
What this formula essentially does is tell the software that once today's date matches the deadline in D2, the cell (or whichever range you selected) will turn red.

If you're interested, you could also drop this formula into C2: =IF(OR(D2="";D2-TODAY()>0);"";"Deadline passed "&" "&(ABS(D2-TODAY()))&" days ago")

Hey everyone,

I have a quick question.
Does anyone know how I might set up a Conditional Formatting rule to flag when a specific date has expired?
So, if I have a cell where I've entered January 1, 2009, and I want it to trigger—meaning, turn the cell red—once three years have passed since that date.
Basically, once we hit January 1, 2012, I'd want that cell to turn red automatically.🙂

Thanks,

Hey everyone...

So, I have a bit of a question... I was wondering how one might go about creating a specific kind of chart in Microsoft Excel, perhaps using a pivot chart?

The idea is that we have two different values—let’s say, actual hours worked versus the maximum allowed hours.

I suppose what I am looking for is a way to have the chart display a light blue bar representing the actual hours worked, and then immediately following that, a dark blue segment showing just how much time is left until we hit that maximum limit.

I've included an example here, if you'd like to see...

It isn't an exact replica of what I'm envisioning, but I imagine the general concept is somewhat clear.


Thanks.

Hey everyone... I had a quick question for the group...

So, suppose we’re working with a pivot chart—specifically a bar chart—that's displaying certain values
where the axis scale goes up to, say, 100
and you have some bars that are taller than others, and I was wondering if there's a way to tell Excel to automatically color all the bars above 50 red, while making everything under 50 blue.

Hey there,

I had a quick question for the group...
So, if I have a column where I've just entered a bunch of 1s and 0s
how would I go about using a count function to make sure the second column only tallies up the cells that actually contain a 1?
Actually, I guess it doesn't necessarily have to be a count function specifically, as long as the logic works and gets me the right number.🙂

Here is a little example of what I mean...


Thanks,

That actually works, surprisingly enough 🙂
thanks

Kyle Johnson2 said:Suppose you're working with a range like A1:A10
A1: leave it without any Data Validation
A2: =COUNTBLANK($A$1)=0
just select the range then go to Data Validation => Custom
A3:A10 =COUNTBLANK($A$1:$A2)=0

Nicholas King said:=COUNTBLANK($A$1)=0

For any other cells, you could probably use something like this for A3:

=COUNTBLANK($A$1:$A$2)=0

though, honestly, you don't really need to—it's pretty much impossible for A1 to be empty while A2 isn't.

If I were following my own internal logic, I suppose

=NOT(ISBLANK($A$1))

should technically work, but for some reason, it just doesn't seem to behave. 🙂

Okay, so that works, but it only blocks entries if A1 specifically is empty.
What I'm actually looking for is a way to prevent someone from entering data if the cell immediately preceding it is blank. For instance, if A1 through A4 are all filled out, I want to make sure they can't skip ahead and type something into A6.
Basically, I want to force them to enter data in a specific sequence.

thanks

Hi there,

I have a bit of a question... I was wondering if anyone might know a specific formula for data Validation that could, say, prevent someone from entering anything into cell A2 unless cell A1 actually has something typed into it?
Basically, I’m trying to ensure that within a single row, entries have to follow a strict sequence—A1, then A2, then A3, and so on...
It’s so you can't type anything into cells A2, A3, or A4 if A1 is still sitting there empty.
And then, if A1 is filled but A2 is still blank, you wouldn't be able to enter anything into A3 or A4 because A2 hasn't been completed yet.

I hope I haven't made things overly complicated with my explanation? 🙂

Thanks,

Hey everyone...

I have a bit of a question.... I've been trying to use this guide to merge several different images together, but I seem to be running into a bit of a snag.

I've followed every single step in the tutorial exactly as it's laid out, but for some reason, I keep getting hit with this error message...



It won't actually pull the image in; instead, it just tells me the link is invalid... which is strange because I'm pretty sure I set everything up correctly.



If anyone happens to have an idea what might be going wrong...

thanks

Thanks.

I suppose it might be worth looking into using MERGEFIELD alongside INCLUDEPICTURE, as that could potentially prove useful for what you're trying to achieve.

Kyle Johnson2 said:I suppose you might be able to handle that through... So, I was thinking about how much easier things used to be before everything became so overly complicated, and it got me wondering about using Mail Merge within Word 2007. It’s one of those features that feels like it should be straightforward, yet sometimes you find yourself staring at the screen, wondering if you've missed a step or if the software is just being difficult, I suppose. If you're trying to get a mass mailing set up, you'll want to make sure your data source is ready to go—maybe an Excel spreadsheet with all your contact info neatly organized—before you even touch the Mail Merge settings. Once you have that, you can start linking the documents together. You'll be looking for those specific MERGEFIELD placeholders to tell Word exactly where to drop in the names or addresses. It’s a bit of a process, and I guess if you aren't careful, things can get messy, but once you get the hang of the flow between the data and the document, it usually settles down. It’s funny how much time we spend just trying to get the formatting to look right, isn't it? Anyway, if anyone has run into specific hiccups with the 2007 version, I'd love to hear how you handled them. I suppose I should probably get around to addressing this, though I can't quite decide if it's worth the effort or if I'm just overthinking things again. Maybe I am. It seems like there might be two ways to look at this, or perhaps it's just an "either/or" situation that I haven't fully unpacked yet. Either way, I guess we'll see how it unfolds. I suppose I was thinking about Word 2003 the other day, which feels like a lifetime ago, though I guess time does tend to slip away like that. It’s funny how much we rely on these older versions sometimes, even when everything else has moved on so rapidly..
There is one rather significant distinction to be made here, which really comes down to the specific way you go about using... I suppose I should probably get around to discussing the intersection of MERGEFIELD and the INCLUDEPICTURE field, though I suspect it’s one of those niche technical hurdles that most people just stumble over without much fanfare. It isn't exactly straightforward, is it? If you’re trying to pull dynamic images into a document via a Mail Merge, you might find that things don't always behave quite as predictably as one would hope, especially when you're deep in the weeds of Word 2007 or even older versions like Word 2003. The general idea, if I’m grasping it correctly, involves using the MERGEFIELD to point toward a file path, which then feeds into the INCLUDEPICTURE command. However, there is this rather finicky little quirk where the images don't always refresh themselves automatically after the merge is complete. You often find yourself having to manually trigger an update—perhaps by selecting everything and hitting a specific key combination—just to get the visuals to actually show up where they belong. It feels a bit clunky, I guess, almost as if the software expects you to hold its hand through the entire process. It’s a bit of a dance, really, navigating the syntax to ensure the pathing is correct so that the engine knows exactly which JPEG or PNG to grab from your local drive or server. One small typo in that string of code and, well, you're left staring at a blank space where a headshot or a logo ought to be. I suppose you’re asking about what exactly needs to be entered manually using the Ctrl + F9 shortcut, though I imagine you might already have a bit of an idea. If I had to guess, you're probably looking to wrap those specific fields in curly braces, since that’s how you trigger the field codes within Word, isn't it? It feels a bit archaic sometimes, almost like we're stepping back into the era of Word 2003, but I guess that's just how the software expects us to behave if we want things to work properly.

Hey everyone...
I have a bit of a question here, if anyone happens to know the answer... it would be a huge help...

Is there actually a way to create a link within a Word document that would automatically pull images from a specific folder and drop them into pre-designated spots?

So, in theory... you would set up a path in the Word document—let's say, grab all the images from C:\Users\Desktop\ATM_Photos\55-11010033
and then have it pull images from another folder like C:\Users\Desktop\ATM_Photos\44-11010022, placing each individual image into its own specific, predetermined location within the document.

I suppose it would be somewhat similar to how a Mail Merge works in Word, where you're pulling data from an Excel spreadsheet into specific fields in a Word file.

thanks

So... I was thinking about this as a sort of digital ledger.
The idea is that if someone types something into A1, just so we don't have any forgetful moments where things get skipped, it should then move on to B1—but at that point, B1 should lock itself up, and vice versa.
Basically, both A1 and B1 start out unlocked.
If there’s a number sitting in A1, B1 locks down, and if there’s a number in B1, A1 gets locked.
For the time being, I've been hacking my way through this using Conditional Formatting, so if there's a value in A1, B1 just turns black, and then it works the other way around.

I gave Data Validation a shot just now using the formula =IF(A1="";"";"")
And honestly, it actually works!
thanks

Kyle Johnson2 said:You haven't quite laid everything out. I suppose I'm wondering why cell B1 is so critical, or if it needs to hold specific data, like if cell A1 happens to be empty.

In theory, you could just prevent anyone from entering anything into cell B1 by unlocking all the cells on the Sheet except for locking cell B1 specifically, and then just applying Protect Sheet (whether you use a password or not). You could even set it up so that cell B1 can't even be selected in the first place.

There is also a possibility using Data Validation where you select the Custom option and plug in the formula =IF(A1="";"";""), and maybe add an input message, though I don't fully grasp the entire scope of the issue, so I can't say for certain.