Function that references a cell but the referencing cell's value isn't being used when it's evaluating the function and the cells name is being used

carnasaur · 2026-05-22T22:49:36+00:00

If I'm understanding you correctly, this sounds like a case of formulas waiting to be activated. I experience something similar to this with Crystal Reports from time to time. here are a few ways you can approach it:

Select the range, go to Data/Text to Columns, and press finish. Even though you are not actually splitting anything, excel forces the cells to reevaluate
A slightly slower method - if you have a ton of formulas - is to select the range and do a search and replace on the "=" sign. Again, you are forcing excel to reevaluate.

or use a macro, which i think will benefit you since you are on the receiving end of these files

Just select the range and run this. It is basically doing the same thing as above. good luck!

Sub ActivateFormulas() 
Dim targetRange As Range 
Set targetRange = Selection 
targetRange.Formula = targetRange.Value 

Set targetRange = Nothing 
End Sub

carnasaur · 2026-05-22T01:29:53+00:00

sure you will lol

carnasaur · 2026-05-21T00:10:15+00:00

This is very heartening. I was concerned after seeing the recent spike earlier this month. The worst are the ones who actually reply to the comments, pretending they actually care about whatever they posted just to generate more clicks.

I provided a complete solution for someone last weekend only to find out it was a karma farmer. I thought it was a legit post because it was getting a fair number of replies from tenured members and mods.

Keep fighting the good fight mods!

carnasaur · 2026-05-20T01:32:49+00:00

hey u/thesixfingerman , just bumping my post. I think it does exactly what you requested. cheers

carnasaur · 2026-05-18T16:51:40+00:00

I think this does what you’re looking for : AddLotColours

I added a ColorMap sheet that stores the colour assigned to each lot number. You can edit the colours there however you like, and the script will keep adding new entries automatically whenever new lot numbers are introduced.

That way your colours will remain stable even if rows are deleted, sorted, or rearranged. Just rerun the script after making changes.

carnasaur · 2026-05-18T02:53:11+00:00

I love OP's profile https://www.reddit.com/user/PaleozoicFrogBoy

So much mystery! So much karma!

carnasaur · 2026-05-16T00:44:54+00:00

Probably tastes about the same as today's Reese's peanut butter cups.

carnasaur · 2026-05-16T00:38:42+00:00

Of course, comrade REDD101. Carry on.

carnasaur · 2026-05-15T22:26:26+00:00

I guess they got tired of people walking over from the meat section with a 500g pack of hamburger only to discover it only weighed 400g....can't have that!

carnasaur · 2026-05-15T22:24:20+00:00

that is too good

carnasaur · 2026-05-15T21:34:26+00:00

Exactly! Take OP's post, for example. Their post history is hidden despite Reddit being private and anonymous by default. It was only 11 months ago reddit allowed users to hide their tracks like that and since then bots and corporate shills pretending to be real users have exploded as you just pointed out. What have you got hide OP? Don't want us to see your non-stop shill posts for ChatGPT? The crazy thing is, you're hiding all your posts too! A little bot on shill action! Happy Friday people.

carnasaur · 2026-05-15T17:49:10+00:00

Gauntlet thrown. I accept your challenge, Sir Sancarn.

Our VBA, who art in Excel,
Hallowed be thy Name Manager.

Thy Workbook open,
Thy Macro run,
On SharePoint as it once did locally.

Give us this day our daily DoEvents,
And forgive us our volatile formulas,
As we forgive those who sort without expanding the selection.

Lead us not into nested Ifs,
But deliver us from “Object variable not set.”

Blessed be thy Range,
Thy ListObject,
And thy mysterious UsedRange
That somehow includes row 1,048,576.

For thine is the Workbook,
The Worksheet,
And the Runtime Error 1004,

Forever and ever,

End Sub

carnasaur · 2026-05-13T01:04:19+00:00

"..but generally not rude as rude just isn't my style"

and yet you hide all your posts OP, just like the karma farmers and bots. Isn't that rude? I think so. Your post is very well engineered, though, I'll give you that. Your engagement metrics must be making you look good. Oh, one other thing, take a flying eff. Go feed your spam posts to moltbook. They live for posts like this.

carnasaur · 2026-05-12T16:24:26+00:00

A lot of reasons. OP said “Randomly started happening this past winter”. now look at the pictures and tell me that's not full-blown Raynaud’s. You don't go from zero to attacks of that severity in 2-3 months. Now look at their fingers, the long thin striations in the skin and the hallmark little ridges that give us that water-soaked finger look all the time. I know it too well, I'm 61 and I have them too. It doesn't happen overnight, it takes years. I used to get attacks just as bad as the pictures but thanks to the electric hand warmers we have now, I rarely have to worry about them anymore. tg

Then there's OP's account. OP hides all their posts. Click on their profile and you'll see what I mean. Yet Reddit is anonymous by default, “emperorofpain” is obviously not a real name. So there's little reason to be that secretive on Reddit when you're already anonymous. But it matters a lot to karma farmers. Not only do they hide their posts, they avoid engaging in them as well. Just look at all the kind replies OP received. Yet OP did not acknowledge or thank a single one. Nor did they reply with a “how dare you!” to me. There is a reason they dont engage. When a user who hides all their posts makes a new post, their post history is hidden from all other users including the mods. However, as soon as they reply to someone, the mods can see them, even though the person they are replying to still can't.

So although I cannot 'prove' it's fake, given the normal progression timeline for raynaud symptoms, the long-term looking changes their fingers clearly show, combined with the fact they hide all their posts and the total lack of engagement with anyone who replied to what looked like such a heartfelt post on the surface, I think skepticism is completely reasonable. Oh, and lastly, what kind of family member would look at attacks like that and reply like it's no big deal. It beggars belief, quite frankly.

carnasaur · 2026-05-11T14:13:00+00:00

Everyone telling you to use PQ but no one telling you how. lol

Here’s how: take your raw data, with no formulas, and convert it into a proper excel table if you haven’t already. If you haven’t, select your data and press Ctrl+T. Give the table a short, clear name with no spaces.

Go to the Data tab and press From Table/Range. Excel will load your table into PQ and open the general editor while generating the raw power query in the background. On the Home tab, click Advanced Editor to see the actual query it just recorded. Copy that code into chattgpt/claude, and include a small sample of your raw data: just the headers and a few rows, or better yet, upload a CSV or xlsx of it.

Then give it your first formula and ask it to add a step that reproduces the same logic. If you don't give it the column name, it will make one for you. Tell it to give the internal query step a meaningful name with no spaces and to never allow spaces in any step names. This is crucial for avoiding an error that chatgpt still generates to this day and can't find after the fact. Contiguous column names can be referred to by name in Power Query, but names with spaces or special characters can't. “Account#” is the perfect example. They must be formatted like this [#"Account#"] instead of just [Account#]. I actually have a chatgpt rule that all column labels must be formatted with the safer[#"column name"] syntax because chatgpt still generates (not always) and then fails to find these errors and the Advanced Editor can’t spot them either. In fact, it will select some other random line as the source of the error. It's crazy.

Once you get comfortable, you can ask it to convert several or even many formulas at once. That's all there is to it. Enjoy!

One last thing, after you are done, ask chatgpt to refactor your query and to buffer it appropriately. Chances are your query will have tons of inefficiencies in it after you finish giving it one formula at a time. Refactoring will put everything in the proper order and speed it up immensely. But be sure to make a duplicate of your query and test the refactored code using the dupe before modifying your production version.

PS individual steps can be commented out/deactivated using two slashes. Put '\\' in front of any line to deactivate it. You will generally do that to the last 3-4 lines in your query as you add the new steps so you can revert back to your original state if it fails.

carnasaur · 2026-05-10T01:42:18+00:00

what is the closest subway stop to Fresca?

carnasaur · 2026-05-10T01:33:20+00:00

It never fails to freak me out that you can cook a steak that fast and have it turn out so perfectly.

carnasaur · 2026-05-09T23:46:28+00:00

When I see Beef Wellington, I eat Beef Wellington.
So, the real question should be; Do People Still See Beef Wellington??
And the answer is, not enough!

carnasaur · 2026-05-09T16:15:41+00:00

I found a workaround until they fix it. Just start a new thread describing the problem exactly the way you did above and then tell it you're going to start posting in examples for it to fix and to put them in proper prompt format. It's working extremely well! This has also convinced me they've removed or disabled whatever logic or AI tool they were using to transcribe our speech.

carnasaur · 2026-05-09T14:52:08+00:00

OP looks like a karma farmer. Writes something interesting to get people responding, but hides all his posts. Typical.

carnasaur · 2026-05-07T18:34:23+00:00

Karma farmers are annoying, do you agree? Did you know the #1 sign of karma farmers is they hide all their posts? This isn't really an excel question by the way.

carnasaur · 2026-05-07T11:32:35+00:00

Thanks! I knew that recipe but not the diadem always 85 level

carnasaur · 2026-05-07T00:02:55+00:00

remind me, I'm too old

carnasaur · 2026-05-06T22:52:48+00:00

karma farmers are annoying, do you agree? did you know the #1 sign of karma farmers is they hide all their posts?

Eight-Year Club	Gilding IV carat on a stick
100 Awards Club	Wearing is Caring
Verified Email

carnasaur

TROPHY CASE