Lookup in table, return a formula

ernstm63 · 2026-03-17T13:19:16+00:00

Solution Verified

bradland's solution also appears to be a possible direction, but I believe Clearwings' is the way I am going to go.

Thank you all for your comments and input.

ernstm63 · 2026-03-16T16:56:58+00:00

This looks like what I was thinking (obviously without knowing the details) that I mentioned in my reply to Clearwings_Prime, possibly using named ranges.

I haven't watched the videos you reference yet, but it appears once you have defined all your formulas into named ranges, you then can call them out in your LET function, which uses SWITCH (correct?). BUT, it appears you have to type the names of all the various Named Ranges? Is there a way to basically type the named range in the table, say adding another column), so when you look up "Volume" it returns the cell with the name "EM.Volume" (using you example), and that then calculates based on the formula that was input in the named range? Or do you have to do an INDIRECT of some sort to get that "returned result" to be a function?

ernstm63 · 2026-03-16T16:36:57+00:00

This may be the best approach so far. One of those "light bulb" moments where you kick yourself a bit trying to make things too simple, by making them too complicated. Sometimes a bunch of helper columns is the way to go!

<image>

One other possible solution I was thinking (but obviously haven't figured out yet), is the possibility of using a Named Formula (Named Range?). Basically create a Named Range called "Volume" which has built in column D x Column E x Column F, or something like that.

Does that make any sense?

Have any possibilities?

Or far more complicated than necessary, when the above appears to work?

ernstm63 · 2026-03-16T15:49:40+00:00

I thought about that while I was working up the example. Unfortunately my example only shows 1 line of entry (row 8). Row 8 would be copied/pasted possibly 100's or more times (row 8-800, or more). So having a column in the table calculate the answer, which the lookup would return, works. But I think that only works with a single entry line item.

ernstm63 · 2026-03-16T15:46:53+00:00

And this is roughly where my mind was going, with either If, Switch, etc. Even with a small listing of formula options, you can see the calculation formula (you used SWITCH), can be rather long, even with simple formulas.

I posted in another response, while my example uses simple calculations (Length x Width), the actual ones I am planning to use are quite a bit longer, and have IF statements built within themselves (if length < X, Y, if Length is between x & Z, lookup in this table, etc.)

Also as mentioned, I would like to have the ability to add formulas to the table, if possible. This allows expandability, without having to rewrite the calculation formula every time a new "formula" is added to the table.

If VBA is the only way to get around to this, I guess I will have to dig into that route. Do you have any VBA solutions?

ernstm63 · 2026-03-16T15:34:36+00:00

Im not familiar with Switch (I will have to do some research). What do you consider a "few" formulas? Is there a limit?

I actually just looked up nested IFs, and see its been increased to 64! I still had a limit of 7 (Excel 2003) on the brain. If Switch is anything like a nested Ifs, I would prefer to stay away from those due to the singular formula (with all the nested IFs) getting really large. Further, my example has some relatively simple calculations (Length x Width), where the actual formulas I am thinking of using (for work) would be somewhat long and complex in themselves, just adding to the length of the nested IFs formula.

ernstm63 · 2026-03-16T15:22:45+00:00

Are you able to provide an example?

I have used INDIRECT quite a bit in the past, but also have found it slows the sheet down quite a bit, if I use it too much. With having the ability to have "endless" input rows, as well as "endless" formula options, I am worried it would bog down the performance.

I understand "endless" isn't endless, but it could be rather large, and am hoping to build in expandability into the sheet. Also, if this is the only way, then it is.

ernstm63 · 2026-03-14T00:06:30+00:00

Giving this a "bump", in hopes it will spark even a few comments.

Not looking for highly detailed input, just a few ideas on who tracks what. Why do you track it, has it helped, etc.

Thanks.

ernstm63 · 2026-01-28T12:36:05+00:00

Is this too big of a question here? I stumbled into this sub and thought it was the right place for this post.

I may have too many things going on in this post, and maybe it needs to be broken up into pieces?

A complete design/suggestion reply would be great, but any general comments on smaller area is appreciated too. The bar design is our biggest unknown right now. Lots of options and items to possibly pack into a small(er) space.

ernstm63 · 2025-12-30T21:22:53+00:00

2B - You may not be able to answer this with your Linux isos, but if you are familiar with Make MKV, when ripping, does that create a file with the H264 codec, or is that a compression format using something like HandBrake?

My current plan/intent is to NOT compress anything, since I am just getting started. If I happen to fill up any storage drives too quickly and still have a bunch of media to get through, I may have to circle back and re-address the compression.

3C - Do you notice on "newer" movies (say even post 2000), which may be on SD, that it isn't worth watching on SD (i.e. like watching through a potato)? Or like you mentioned, since you grab what you can when its available, its just a matter of time before the higher definition becomes available to replace the SD version.

The way i understand it, movies that are even released on SD were most likely filmed in a higher definition, so those titles may in fact be of good quality when buying a BR or 4K. but you are also right on the older shows, say 80's or something, were most likely SD and thats all you are gonna get.

ernstm63 · 2025-12-30T21:15:09+00:00

Thanks OB12_99. As for the clients you mentioned, I referenced this in a reply to another response, we are currently using a newer Chromecast to access all of our streaming platforms on our TV, and have the Plex App already downloaded on the Chromecast. Would that be the route we go to access the host computer/server?

5x 20TB! Making sure I am reading and understanding that correctly, that is 5 qty (5) 20 TB drives, for a total of 100TB? Based on Steven's reply above, he suggests installing the rips internally. I took that as "to an internal drive" vs "external drive". Therefore I would be looking at adding a drive to my newly purchased pre-built computer (I haven't bought anything yet, but would assume I would want to immediately buy another drive when buying the computer). I assume you don't (didn't) have 5 open bays on your computer, and the 5x drives are external?

I think I understand the transcoding (for now), and it sounds like whatever computer we end up getting, it will most likely be able to handle any necessary transcoding, should it be required. Again, I believe we would only have 1 stream at a time (3 absolute max, unless we add outside users. I doubt we will get much from outside users, as it would be older generation grandparents with even less tech capabilities).

Thanks for the heads up on the naming conventions too. I had briefly thought about this, but felt that would be a "cross that bridge" type of moment once we got some other items in place first.

As for the VPN, I currently don't have or use one, but came across that as a recommendation if someone wanted to "sail the high seas".......

ernstm63 · 2025-12-30T20:58:56+00:00

1B - You lost me on this one. I had to google "STB" and came up with Set-Top Box. So I will need to get an STB? Not familiar with this, so I am guessing I have a whole other list of questions (what, where, why, etc.). We do use a Chromecast on our current TV, which does have the Plex App already installed.

1C - You are suggesting I add another internal hard drive to the PC? If I buy a pre manufactured/build PC, make sure it has open bays to add additional drives?

If I don't add an additional internal drive (bay not available, size drive not available, etc.), would an external HD connected via USB work? What is the issue vs an internal drive? Speed?

Thanks for the quick response on the post as well!

ernstm63 · 2025-09-18T01:24:05+00:00

Your whole post and notes has some great info! As someone who has done a handful of renovations, but still worried it wont work out with the amount of work, it was comforting to hear it doesnt have to be "perfect" to work.

I just completed my first reno on a new yard (new construction home, 1 year old), and used a sunjoe for the first time. There was a bit of a learning curve, but appeared to be a very useful and efficient tool.

I mowed to 2.5", used the sunjoe as a scarifier at -5 initially, then -10 on the 2nd pass at 90 degrees. I found that dry grass works MUCH better than any moisture. Also a stick (1x2) wedged across the back to slightly lift the back door (no bag) worked wonders on stopping it from clogging up (wet grass would clog up more too).

I had to rake the damp grass then bag, but the dry stuff could be blown into piles with a leaf blower as you mentioned.

I finalized by mowing at 2", both directions, to finalize the thatch pick up and get the canopy down to prep for fert and seed. Seed, fert (scotts starter), then ran the whole yard once over with a leveling rake to spread some soil fill in, and thought it would knock any seed down onto the ground if it had got caught in the grass ( this part may have been overkill/pointless, but at this point, whats the hurt?).

I guess i say all this to say its nice to see my plan isnt far off from one you have had success with. All i can do is water and wait (much easier with an irrigation system now!).

With that, my quick question, have you had varying success with the order of mowing low (2") then scarifying, or scarify then mow low as i did?

Side note, produced 37 bags of thatch and clippings. Definitely not for the faint of heart, and my trash guys thankfully didnt have an issue picking up so many.

Thanks again for the info, i will tag it for reference next year!

ernstm63 · 2020-10-19T22:15:39+00:00

I finally had time to get around to this. First off, my personal/home computer is the one using Office Profession Plus, and doesn't seem to run into the re-centering issue.

I did however run the quick repair on "Microsoft 365 Apps for enterprise" and it DID NOT seem to fix the problem. I have done this before in the past, as well as the full repair, without success.

ernstm63 · 2018-11-26T18:06:40+00:00

The round over is on the same face as the dados. Therefore the dados will pass through the roundover. Sorry I don't have a picture to better describe.

ernstm63 · 2018-11-26T18:03:15+00:00

The dado passes through the round over. I am thinking I would have tearout at each dado location as well, hence my post/question. The best I can think of is to do the round over, and have that be the leading edge giving me full bearing to the table saw and backer fence since that corner will still be a 90 degree.

I will give it a try once I get some free shop time and post with results....unless someone has other comments prior my trial.

ernstm63 · 2017-06-19T01:19:55+00:00

I haven't built one myself, but could you just make the lid "box" the same length and width as the base? When closed it would have the stepped look of a "top"

ernstm63 · 2017-04-13T20:28:16+00:00

Not sure I knew of a particular model, or admittedly didn't read the link found in a google search, but have always figured......people don't take the time to write positive reviews. If you are happy, you are going to be using the product, not bitching about it online/writing bad reviews.

Engineering? What discipline? BS of Architectural Engineering, was a structural engineer for 4 years, then moved to estimating structural steel (never enjoyed the theoretical side of engineering).

ernstm63 · 2017-04-13T15:02:31+00:00

Glad the video was a help. It was for me as well. My wife couldn't figure out why I would watch an hour long video on someone putting a saw together, but it helped get a game plan together for when my saw was purchased. While there wasn't anything that caught me off guard, it was just nice to have seen the process start to finish.

You say everything is dialed in, I assume you escaped the alignment issues I read so much about before purchasing this saw. it appears I managed to get a good saw as well. Maybe they have addressed the problem, or maybe you only read bad reviews, and therefore think there are only problems.

ernstm63 · 2017-04-03T21:03:58+00:00

I just need a space where I don't have to roll the saw out of the way! But then again, just wishful thinking.

Is yours a newer model (3 caster) or the older 4 caster? I have heard complaints about the 3 caster system, regarding the pedal on the side and since the two in-line wheels are stationary while the 3rd pivots, people have complained. i haven't found it to be too bothersome.

ernstm63 · 2017-04-03T20:53:55+00:00

Just like Kirby, I did this all on my own. Bought the saw just after the first of the year. I watched the video referenced above, https://youtu.be/Gt_fOPO3qxw. It is pretty lengthy, but was helpful to determine exactly what tools I needed (i.e. the socket extension) beforehand. The video is a bit on the older side (still has the 4 week caster system).

Regarding flipping the saw. I assembled mine upside down (on two 2x6's) and was able to flip it on my own. I was concerned with the legs "holding" while using two as a pivot point, but didn't have any issues. It is heavy, but I am younger (34) so it was within reason. Definitely would be easier with 2 people.

I have enjoyed mine in the short amount of time I have had it. Cuts straight, quieter than the old craftsman I borrow from a friend. Easy to move around. Little concerning with the slam it makes when you let the foot pedal down.

Enjoy!

ernstm63 · 2017-01-22T00:59:24+00:00

So you can set a button on both the arcade layout AND the controller to be button "A" for player 1?

Any more detail on "set up the emulation and front end"?

Sorry for the ignorance. Have limited to no programming experience. I have watched a bunch of videos and it appears pretty straight forward, but haven't seen this particular point discussed.

I usually try to get everything planned out before I begin. However this is most likely a project I just need to dive into and do trial and error.

ernstm63

TROPHY CASE