| Be Excellent To Each Other https://www.beexcellenttoeachother.com/forum/ |
|
| Excel help https://www.beexcellenttoeachother.com/forum/viewtopic.php?f=3&t=9988 |
Page 3 of 3 |
| Author: | devilman [ Tue Feb 18, 2020 20:53 ] |
| Post subject: | Re: Excel help |
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  '
|
|
| Author: | Sir Taxalot [ Wed Feb 19, 2020 0:43 ] |
| Post subject: | Re: Excel help |
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) |
|
| Author: | markg [ Wed Feb 19, 2020 8:29 ] |
| Post subject: | Re: Excel help |
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. |
|
| Author: | MaliA [ Wed Feb 19, 2020 9:12 ] |
| Post subject: | Re: Excel help |
This isn't the talk of winners, here, lads. |
|
| Author: | myp [ Mon Jul 27, 2020 15:03 ] |
| Post subject: | Re: Excel help |
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
|
|
| Author: | Cras [ Mon Jul 27, 2020 15:24 ] |
| Post subject: | Re: Excel help |
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 |
|
| Author: | myp [ Mon Jul 27, 2020 15:24 ] |
| Post subject: | Re: Excel help |
Oh yeah, that’s nice. Thanks. |
|
| Author: | Mimi [ Fri Jul 31, 2020 17:21 ] |
| Post subject: | Re: Excel help |
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. 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. |
|
| Author: | Malc [ Fri Jul 31, 2020 17:27 ] |
| Post subject: | Re: Excel help |
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 |
|
| Author: | Mimi [ Fri Jul 31, 2020 17:35 ] |
| Post subject: | Re: Excel help |
It’s ok, I’ve got it. Thank you though x Attachment: Screen Shot 2020-07-31 at 17.55.17.png
|
|
| Author: | Trooper [ Fri Jul 31, 2020 17:36 ] |
| Post subject: | Re: Excel help |
Assuming I've got what you were trying, i'd do something like this: 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. |
|
| Author: | Malc [ Fri Jul 31, 2020 17:42 ] |
| Post subject: | Re: Excel help |
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 |
|
| Author: | Mimi [ Fri Jul 31, 2020 17:58 ] |
| Post subject: | Re: Excel help |
Trooper wrote: Assuming I've got what you were trying, i'd do something like this: 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")
|
|
| Author: | Mimi [ Fri Jul 31, 2020 17:59 ] |
| Post subject: | Re: Excel help |
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. |
|
| Author: | MaliA [ Thu May 13, 2021 10:44 ] |
| Post subject: | Re: Excel help |
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? |
|
| Author: | MaliA [ Thu May 13, 2021 11:28 ] |
| Post subject: | Re: Excel help |
Oh so close now. |
|
| Author: | MaliA [ Thu May 13, 2021 11:50 ] |
| Post subject: | Re: Excel help |
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? 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. |
|
| Author: | Jem [ Tue Aug 03, 2021 10:16 ] |
| Post subject: | Re: Excel help |
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 
|
|
| Author: | Cras [ Tue Aug 03, 2021 10:26 ] |
| Post subject: | Re: Excel help |
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)" |
|
| Author: | Jem [ Tue Aug 03, 2021 10:32 ] |
| Post subject: | Re: Excel help |
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  )
|
|
| Author: | MaliA [ Tue Aug 03, 2021 10:40 ] |
| Post subject: | Re: Excel help |
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. |
|
| Author: | Jem [ Tue Aug 03, 2021 11:00 ] |
| Post subject: | Re: Excel help |
That sounds easier than the pivot thing, I'll do that. |
|
| Author: | Sir Taxalot [ Wed Aug 04, 2021 1:40 ] |
| Post subject: | Re: Excel help |
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). |
|
| Author: | DavPaz [ Mon Oct 17, 2022 16:16 ] |
| Post subject: | Re: Excel help |
How do I make a formula return a blank cell and not just an empty string? |
|
| Author: | Joans [ Mon Oct 17, 2022 16:25 ] |
| Post subject: | Re: Excel help |
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. |
|
| Author: | DavPaz [ Mon Oct 17, 2022 16:26 ] |
| Post subject: | Re: Excel help |
Cheers, I'll try it |
|
| Author: | DavPaz [ Mon Oct 17, 2022 16:28 ] |
| Post subject: | Re: Excel help |
Yep, that worked for what I needed. Thanks Excel Guru |
|
| Page 3 of 3 | All times are UTC [ DST ] |
| Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group http://www.phpbb.com/ |
|