Excel People

[The Fish 10837]

12 hours ago, wykikitoon said:

I used this;

=SUMPRODUCT(--(G$7:G$1048576="GH"),J$7:J$1048576)

... what exactly are you trying to return? just the number of days worked by each employee?

[wykikitoon 20105]

Aye.

Its worked how I want it to.  But if I could do a better way that would be appreciated.

[The Fish 10837]

Either do as ewerk says and list the initials of the employees vertically, Or if you need to have it horizontal use a vlookup like so;

If AB has multiple entries, you might want to use a pivot table

[Howmanheyman 33082]

I just want everyone participating in this thread to know that I'm not impressed with them.

 

 

 

 

 

 

[ewerk 30528]

Someone is clearly bored tonight.

[Rayvin 5217]

I have a question:

 

I'm trying to create a dropdown field that is variable in terms of the data it accesses based on a separate dropdown field. Yes I know I should use an actual database software for this but I am where I am.

 

So in effect, if field A1 is a drop down with the following list:

 

Computers

Peripherals

Misc

 

I want the user to select from that dropdown, and then for field B1 to offer a variable sublist based on their input. So there might be 5 models showing up in B1 if 'Computers' is selected, maybe 20 items if peripherals is, etc.

 

I would also appreciate guidance on how to set up the data sources for the sublists - I'm hoping to have each variably accessed data set in a separate sheet.

 

[strawb 4246]

Honestly be a lot easier to use access for stuff like that I think mate

[Rayvin 5217]

Agreed tbh. Thanks mate.

[The Fish 10837]

19 hours ago, Rayvin said:

I have a question:

 

I'm trying to create a dropdown field that is variable in terms of the data it accesses based on a separate dropdown field. Yes I know I should use an actual database software for this but I am where I am.

 

So in effect, if field A1 is a drop down with the following list:

 

Computers

Peripherals

Misc

 

I want the user to select from that dropdown, and then for field B1 to offer a variable sublist based on their input. So there might be 5 models showing up in B1 if 'Computers' is selected, maybe 20 items if peripherals is, etc.

 

I would also appreciate guidance on how to set up the data sources for the sublists - I'm hoping to have each variably accessed data set in a separate sheet.

 

 

I've done something like that, if I understand it right...

 

Drop down returns an array of results elsewhere? Or do you want drop down A, to populate dropdown B?

[Gemmill 44771]

Dave, I think you've put him to sleep mate. 

[Rayvin 5217]

Damn, I didn't even notice the reply. Sorry mate!

 

It's still relevant tbf, I haven't moved on from this beyond deciding that I need to do some more detailed system development work to make it happen. I want the list in B to present a range of variables depending on the content in A. So in effect, there will be 3 or 4 potential lists for B, and the relevant one is selected once A is populated.

 

Clear as mud, right?

[The Fish 10837]

On 5/30/2020 at 15:53, Rayvin said:

Damn, I didn't even notice the reply. Sorry mate!

 

It's still relevant tbf, I haven't moved on from this beyond deciding that I need to do some more detailed system development work to make it happen. I want the list in B to present a range of variables depending on the content in A. So in effect, there will be 3 or 4 potential lists for B, and the relevant one is selected once A is populated.

 

Clear as mud, right?

 

 

This explains it better than I could;

 

To be clear, the idea is to select a value from a dropdown list, then have different values appear in other dropdowns based on the selection in the first one - is that correct?

If so, you can do this with Named Ranges.

Say we have our lists on Sheet2. You should have a master list or key list, the one that's going to control which of the other lists will be used. Each of the other lists should have a header that matches one of the entries on the master list. For the moment, I would suggest not using values that have spaces in them; there's a way to work around those, but it's more complicated.

Create named ranges for each of these lists. The easiest way to do it is by highlighting the whole range that contains all the lists and going to Formulas | Create from Selection. In the window that appears, "Create names from values in the:", check Top row and uncheck everything else, then click OK.

This has created a set of named ranges, one for each of our lists. Now you can create your master dropdown. Go to the cell that should have the dropdown and use Data | Data Validation. Choose "Allow: List" from the dropdown. In the "Source" box that appears, type in the name of your master list. Hit OK.

Go to the cell that should have the next dropdown. Go to the same window, Data | Data Validation, choose Allow: List. This time, in the Source: box, you're going to put in a formula: =Indirect($A$1), where $A$1 is the cell reference of your master list. Hit OK.

You should now find that when you make a selection from your master list, the options in the next dropdown are based on the named range that matches the entry you selected in the master list. You can repeat this as often as you need to make cascading dropdowns, though the naming can get messy after more than a couple of levels!

 

 

[Rayvin 5217]

Just for the record, this stuff worked and I have it set up now. Thank you.

 

I have a new issue though.

 

I have a table that has each individual Product on separate rows. The Product consists of variable Components that can be selected through drop down boxes in columns D:Z.

 

I am tracking the number of Products sold in Column C.

 

I have an inventory sheet that tracks stock levels for all variable Components, and I want it to update automatically if a Product is sold, based on the variations of Components associated with it.

 

This means I need a formula which will look across D:Z for all rows, identify the Component, and then multiply it by the number of Products sold (Column C). The problem is that I only want it to multiply it if it actually exists as a variable option for that Product.

 

So for example:

 

Product A =  Component A + Component B + Component C

Product B =  Component B + Component C

Product C =  Component A + Component C

 

Product A Sales: 1

Product B Sales: 1

Product C Sales: 1

 

So I want the stock sheet for Component C to count all the sales totals because it is used in all Products. However, Component B I only want it to count Product A and B.

 

Because the Components are all set to a stock value of 1 when they are associated with a Product, it would also be feasible to simply Sum the sales column where a Component exists on the same row. But for the life of me, I can't make it happen. It keeps setting it to zero.

[The Fish 10837]

On 7/6/2020 at 15:06, Rayvin said:

Just for the record, this stuff worked and I have it set up now. Thank you.

 

I have a new issue though.

 

I have a table that has each individual Product on separate rows. The Product consists of variable Components that can be selected through drop down boxes in columns D:Z.

 

I am tracking the number of Products sold in Column C.

 

I have an inventory sheet that tracks stock levels for all variable Components, and I want it to update automatically if a Product is sold, based on the variations of Components associated with it.

 

This means I need a formula which will look across D:Z for all rows, identify the Component, and then multiply it by the number of Products sold (Column C). The problem is that I only want it to multiply it if it actually exists as a variable option for that Product.

 

So for example:

 

Product A =  Component A + Component B + Component C

Product B =  Component B + Component C

Product C =  Component A + Component C

 

Product A Sales: 1

Product B Sales: 1

Product C Sales: 1

 

So I want the stock sheet for Component C to count all the sales totals because it is used in all Products. However, Component B I only want it to count Product A and B.

 

Because the Components are all set to a stock value of 1 when they are associated with a Product, it would also be feasible to simply Sum the sales column where a Component exists on the same row. But for the life of me, I can't make it happen. It keeps setting it to zero.

Can you upload an image that makes this a bit clearer?

 

In my head it sounds like you're asking Excel to know which components go with which product. Is that right?

[Rayvin 5217]

27 minutes ago, The Fish said:

Can you upload an image that makes this a bit clearer?

 

In my head it sounds like you're asking Excel to know which components go with which product. Is that right?

 

 

For the above, each product can have one of 3 variables assigned to it, and tracks the total sales of the product in column B. In columns E:G we have the stock levels of each of the components. What I want is to be able to deduct the number of components that sold as part of the product sale, from the stock levels for those components. So I need something that will pick up on Component 1 from both Variable columns, and will deduct 2 for Product A, and 5 for Product C. And nothing for B and D (for that Component only).

I should add that I don't have the component stock level here in reality, it's on a different sheet - but for simplicity I've added it in E:G.

Edited July 8, 2020 by Rayvin

[The Fish 10837]

Right, 

 

I'd rejig the table a bit for a start. Total Sold in Column D. Also, is the naming convention written in stone or can you change it? Not a huge issue either way, but it just makes things neater if you can fuck around with it.

All that said it's a pretty straight forward countif formula

=I2-($E3*(COUNTIF($C3:$D3,I$1)))

 

I2 is your Stock Total for component 1

E3 is the Total Products Sold for Product A

Countif the range C3:D3 (Product A's components) has Component 1(I1) in it.

Stock - (products sold x number of component 1)

 

Drag that formula across (keeping the $ signs in place) for the three component columns and down as many rows as you have products.

 

Is that what you're after? Orrrr do you want 1 line for all products?

 

[Rayvin 5217]

29 minutes ago, The Fish said:

 

Is that what you're after? Orrrr do you want 1 line for all products?

 

 

First off, thanks for your effort on this - greatly appreciated, even Reddit hasn't bothered coming back to me on this query.

 

But in answer to your ultimate question, yes, I'm after one line for all products. The Component stock sheet is totally separate and fairly complicated in and of itself so I can't run that stock table in it - all I'm looking for is to be able to subtract the sum total of all Component stock reductions from the overall total in stock, in that sheet, in a single line.

 

=(SUMIF('Incoming Stock'!D:D,'Product Inventory'!B:B,'Incoming Stock'!F:F))-(VLOOKUP([@ID],'Sales Log'!E:G,3,FALSE))

 

That's the code I'm running so far. The red bit is being informed by an incoming stock table, the yellow is deducting component sales if we sell them individually. This part works - I just need it to net off as a single number, all components sold across all Products (as variables), on a component by component basis.

 

Your example above does this on a line by line basis that can operate cumulatively... and I suppose I could probably arrange for it to do that in a separate sheet and then sum the columns per component to get what I want. Tbh that would probably work and would fix the issue, I think maybe I was aiming too big to get it all through in a single run of code. Unless you can think of anything on the single line front?

 

Like I said, thanks so much for taking some time on this.

Edited July 8, 2020 by Rayvin

[The Fish 10837]

2 minutes ago, Rayvin said:

 

First off, thanks for your effort on this - greatly appreciated, even Reddit hasn't bothered coming back to me on this query.

 

But in answer to your ultimate question, yes, I'm after one line for all products. The Component stock sheet is totally separate and fairly complicated in and of itself so I can't run that stock table in it - all I'm looking for is to be able to subtract the sum total of all Component stock reductions from the overall total in stock, in that sheet, in a single line.

 

=(SUMIF('Incoming Stock'!D:D,'Product Inventory'!B:B,'Incoming Stock'!F:F))-(VLOOKUP([@ID],'Sales Log'!E:G,3,FALSE))

 

That's the code I'm running so far. The red bit is being informed by an incoming stock table, the yellow is deducting component sales if we sell them individually. This part works - I just need it to net off as a single number, all components sold across all Products (as variables), on a component by component basis.

 

Your example above does this on a line by line basis that can operate cumulatively... and I suppose I could probably arrange for it to do that in a separate sheet and then sum the columns per component to get what I want. Tbh that would probably work and would fix the issue, I think maybe I was aiming too big to get it all through in a single run of code. Unless you can think of anything on the single line front?

 

Like I said, thanks so much for taking some time on this.

 

No worries at all, keeps me from doing actual work (which is also fucking around on excel ironically).

 

How about instead of Variable columns you have Component columns? Is that doable? It would make things way, way easier. Just a formula like =VLOOKUP($A10,$A$2:$E$6,5,FALSE)*VLOOKUP($A10,$A$2:$E$6,2,FALSE) where the first lookup is finding the number of Product A's and the second is the Component 1 column

 

[Rayvin 5217]

3 minutes ago, The Fish said:

 

No worries at all, keeps me from doing actual work (which is also fucking around on excel ironically).

 

How about instead of Variable columns you have Component columns? Is that doable? It would make things way, way easier. Just a formula like =VLOOKUP($A10,$A$2:$E$6,5,FALSE)*VLOOKUP($A10,$A$2:$E$6,2,FALSE) where the first lookup is finding the number of Product A's and the second is the Component 1 column

 

 

So the problem is that the variable situation is actually way more complicated than what I've outlined here. The variable columns would be better defined as 'category of variable columns'. And they have drop down boxes for up to 10 or 20 specific subsets of that variable category. So I get what you're saying but no, sadly not.

 

I do appreciate that all of this would be far easier in an SQL database but I can't afford to spend the time on that yet. New venture etc etc.

[The Fish 10837]

A stitch in time and all that mate.

 

If you've a dataset with a large number of variables, Excel will let you down. You end up needing multiple tables and possibly Macros. You're right that a database would be better, but if you've Access you could knock something useful up pretty quick. Table with all the Product, Table with all components, Table with stocks. 

 

If you've got a stack of products, each from a multitude of components then there's just too many variables. 

 

I caveat all of this by admitting that I'm a moron.

[Rayvin 5217]

10 hours ago, The Fish said:

A stitch in time and all that mate.

 

If you've a dataset with a large number of variables, Excel will let you down. You end up needing multiple tables and possibly Macros. You're right that a database would be better, but if you've Access you could knock something useful up pretty quick. Table with all the Product, Table with all components, Table with stocks. 

 

If you've got a stack of products, each from a multitude of components then there's just too many variables. 

 

I caveat all of this by admitting that I'm a moron.

 

I got it.

 

SUMPRODUCT(SUMIF(C3:D7,"Comp 1",E3:E7))

 

I didn't get there myself, I did a final spate of desperate googling and found it. Tbf mate, everything you've set out would be fine for a single line and I suspect that if you'd not contributed then I wouldn't have got there in the end. Thank you.

[Gemmill 44771]

Excel gimps. Has anyone used Power Query? I cannot believe this thing exists and that I'm only just finding out about it. It's fucking unreal. 

[Gemmill 44771]

Absolutely absurd that there hasn't been a HUGE response to my post above.  Basically if you're doing ANYTHING with data in a spreadsheet, using Power Query will probably earn you at least a day off a month. 

[Christmas Tree 4725]

10 minutes ago, Gemmill said:

Absolutely absurd that there hasn't been a HUGE response to my post above.  Basically if you're doing ANYTHING with data in a spreadsheet, using Power Query will probably earn you at least a day off a month. 

[Gemmill 44771]

HALFWITS NEED NOT APPLY.