Cras wrote:
Also demonstrating serious proficiency with excel usually involves saying "don't use Excel for this"
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
' 
Cras wrote:
Also demonstrating serious proficiency with excel usually involves saying "don't use Excel for this"
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.
Code:
=((I2*C2)+(I3*C3)+(I4*C4)+(I5*C5)+(I6*C6)+(I7*C7)+(I8*C8)+(I9*C9)+(I10*C10)+(I11*C11)+(I12*C12)+(I13*C13)+(I14*C14))/C15
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 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.
Attachment:
excel screen.png
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
Attachment:
Screen Shot 2020-07-31 at 17.55.17.png
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.
Code:
=IF(B5<>"","with","")
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.
Attachment:
formimi.png
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
Trooper wrote:
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.
Code:
=IF(B5<>"","with","")
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.
Roundabout:
Code:
=IF(B5="","","with")
Malc wrote:
Attachment:
formimi.png
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
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?
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.
MaliA wrote:
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?
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?
Code:
=IFERROR(INDEX(PO!$P$3:$P$33333, SMALL(IF(PO!$F:$F:F=$A6, ROW(PO!P:P)-ROW(PO!$P$3)+1), COLUMN(A5)}},"")
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 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?
DavPaz wrote:
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
Page 3 of 3 [ 127 posts ]