Hi,
Explanations are also found in the spreadsheet.
In a table I have several modes, with items and various quantities.
When I choose a mode in a dropdown, then I want to present the relevant items for that mode, if they have a qty attached to it.
Please no volatile functions or VBA.
Hope you can help.
Best regards,
Trond Grimstad
I get by with the help of some helper columns.
The first calculation is in Cell G1 that is the column number of the sub-table in columns C:F. We calculate that quantity once.
Column G is filled in using the INDEX command against the sub table.
Column H is used to determine if the value in column G is zero. If so, we don't want it in the pivot table. The pivot table is shown in columns S:V.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
No helpcells, with a counted field in the pivot table.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Hi,
Thank you for good proposals, but could we manage without the pivot?
just with formulas in the blue table?
Trond
what is the reason not using pivot table?
it is a very powerfull tool which is expanded in every new version of Excel, so MS is telling us that is the way to go (future).
Item
=IFERROR(INDEX($A$3:$A$8,SMALL(IF(OFFSET($A$3,0,MATCH($J$1,$B$2:$F$2,0),COUNTA($A$2:$A$8)-1,1)>0,ROW($A$3:$A$8)-ROW($A$3)+1,""),ROWS($A$3:A3))),"")
Enter with Ctrl+Shift+Enter
(OFFSET is "volatile") but I am not sure (don't know!) if it can be done any other way. You may need a helper column to avoid volatile/array functions if PIVOT tables are a "no-no".
Unit Rate
=INDEX($B$3:$B$8,MATCH($K3,$A$3:$A$8,0))
Quantity
=INDEX($B$3:$F$8,MATCH($K3,$A$3:$A$8,0),MATCH($J$1,$B$2:$F$2,0))
Sum (!!)
=L3*M3
Last edited by JohnTopley; 06-23-2016 at 03:18 AM.
oeldere,
Thanks for help.
Pivot is not the preferred solutions because this extract is part of a larger overview with several elements.
Consideration related to design, user interface.
Trond
JohnTopley,
Thanks for solution.
As far as I can see now, this will be perfect.
Thanks!
Trond
If your problem has been solved can you mark the thread as such ("Thread Tools" at top of first post).
Thank you for the feedback.
Thank you for the feedback!
It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.
JohnTopley,
Do I understand the formula correct, that both tables need to be located on the same row?
Is it possible to have the extracted list on a separate tab?
Sorry for not being clear from the start.
Trond
They can be on separate tabs and any range so you need to change the range reference accordingly.
Try it and if you have problems post a file with your "live" ranges.
After some testing, I understand it, and it works!
Thanks!
Well done! Best way to learn is "DIY"
.. If you are OK with your results, can you mark thread as solved ("Thread Tools" at top of first post)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks