Be Excellent To Each Other

One of my colleagues was on the phone with someone from Microsoft support today because something we were doing was working and we couldn't see how it was possible and their response was basically 'That shouldn't work '

Yeah, I get what you're saying and it's an amusing way to phrase it - Excel is a tool that isn't suitable for all purposes BUT it is still very capable and for many is a familiar way of working so I do see why a lot of people use it as their first go-to (indeed, I do)

Yeah, it can get out of hand, though. Quite a few of the things we've done at work are to replace some insane excel solution that's causing issues, but then there were probably bigger issues that got solved by their ad-hoc solution so I can see both sides.

This isn't the talk of winners, here, lads.

There must be a more efficient way of doing this. Any idea? I'm rubbish at Excel.

You could have a column J where J2=I2*C2 then copy that formula all the way down. Then at the bottom of J (which is presumably J15) have the formula sum(J2:J14) then have a cell that is just J15/C15

Oh yeah, that’s nice. Thanks.

Apologies for this being a dumb-as-rocks question, especially as my entire job used to revolve around Excel and I was pretty damned good with it, but having a baby turned my brain to potato.

I am making a meal planner (again, potato brain) with drop downs that feet in from the secondary sheets.

I want a text field in a cell to only show if another cell is populated by text from one of the options in a drop down. Does that make sense? Maybe a screenshot will explain better than I can.



So, where it says 'with' or 'and' - I only want to show those if the thing has an accompaniment. So Bagel WITH cheese and ham, but not French Toast WITH <blank> - I just want that to say 'French Toast'.

It's in this kiddy's learning font because I'm making it for a little'un.

I would make a table with the main part of meal in one column and the extras in subsequent column and then use vlookup I think

It’s ok, I’ve got it. Thank you though x

Assuming I've got what you were trying, i'd do something like this:



in the "with" boxes, and something similar in the "and" boxes. B5 being the cell under French Toast in this example, so if B5 is not empty, it shows with, if it is empty it doesn't show anything.

I think, haven't actually fired up a spreadsheet to test it.

Something like that

I have called the first table "mains" and then in the cell to the right of French Toast, Sanwich Thin and Bolognese I have a formular like this:

=VLOOKUP(M4,mains,2,FALSE)

in the cell below that (where the first and would appear) I have

=VLOOKUP(M4,mains,3,FALSE)

and the one below that (where the 2nd would be appear) I have

=VLOOKUP(M4,mains,4,FALSE)


Bit Clunky, but hopefully you get the idea

Roundabout:

I do, but I went with the other way as I'd already 99% set it up that way, but thank you

Honestly, I'm pretty sure my mind is permanent fudge now, but it's definitely been worse since lockdown.

Hello.

I can't work this out.

Column A has product numbers. In columns J,K, and L I want the delivery dates. These sit on a separate worksheet (product number in column F and delivery in date in P. One product can have more than delivery dates as separate orders.

I think I need an index function and array it, is that right?

Oh so close now.

Pleased with that.

Hellloooo, I need excel help please!

I have a spreadsheet. In the first sheet are 2000+ products, with the following headings:
Vendor Product code Product name Category Secondary categories Price Quantity Status Shipping freight Free shipping Description Taxes Inventory tracking Zero price action Page title Meta keywords Meta description Search words Options Short description Out of stock actions Min quantity Language Product id Image URL Items in box Box size SEO name Weight Product URL

In the second sheet are 1000ish products (originating from sheet 1 but with the unwanted ones culled).

There are different column headings in sheet 1 and sheet 2, but they are the same products.

I need to put all of the rows from sheet 1 that exist in sheet 2 into sheet 3 - hopefully using the SKU (which is "Product code" in sheet 1, "SKU" in sheet 2).

I thought this - https://stackoverflow.com/questions/563 ... tching-key - would help but honestly it just made me more confused and I'm at the point where I will literally just pay someone to do this for me if I can't figure it out in the next few hours

I'm assuming you've got access to a mySQL db - it might be easiest to just load both sheets into a couple of tables and then just use something "Select * from table1 where product_code in (select SKU from table2)"

Yeah, that was going to be my backup plan if I couldn't figure it out in excel. (I could have probably done it 10 times over in mysql by now. Stubborness sucks )

I'd add a copy sheet 1 to sheet 3, and add a column at the end doing a VLOOKUP against sheet 2, , filter on #N/A and delete them.

That sounds easier than the pivot thing, I'll do that.

Much like Mali, I was also going to say it looked like VLOOKUP would be the solution to me (I can't do SQL or VBA though, so there may be a better way if you need to do this often).

How do I make a formula return a blank cell and not just an empty string?

Not answering your question, but countblank will count "" returned from a formula, so that might help.

Cheers, I'll try it

Yep, that worked for what I needed. Thanks Excel Guru

Excel Help please.

I'm tracking my son's football team's results and the coach has asked me to add assists to the stats. I've been able to use the same formula that I use for tracking MOTM for now, where a countif checks a cell for a certain string, but it occurs to me that someone might get more than one assist in a game.

=countif(League!B24:Z24,"*PLAYERNAME*")

Does anyone have a way of counting how many times a certain string appears in a given cell?

Ta.

sumif?

Is it that simple?

I haven't looked, but I am pretty sure I was able to use sum if in a similar situation (where I needed to sum multiple cells, rather than just count them) If that doesn't work, let me know, and I'll try and actually mock up some data and see what's needed

No, that just returns a zero. It needs to count all occurrences of a string across a range of cells where the string could appear multiple times in each cell, with other strings.

You can see why I'm struggling to wrap my head around it

Can you provide sample data? (with the names removed)

=(LEN(B2)-LEN(SUBSTITUTE(B2,"sheila","")))/LEN("shiela")

That'll count for a specific cell, add the cells up for a range of cells I guess

But what if they weren't called Sheila?

Thanks gents. I've copied the system I use to track goals.

Sounds like the data being input isn't all that helpful to begin with, why is there not just a # for goals (be it 0, 1, 2 whatever) and the same for assists.

Pleasant memories of Championship Manager 96/97 come flooding back.....ahhhhhhhh, good times