Kindly help how to revise this array formula:
I have added &"^"&INPUT!$B$4:$B$100="materials" to the above formula as I need A=x and B=materials - it did not work. Please see attachment.Please Login or Register to view this content.
Thank you.
Kindly help how to revise this array formula:
I have added &"^"&INPUT!$B$4:$B$100="materials" to the above formula as I need A=x and B=materials - it did not work. Please see attachment.Please Login or Register to view this content.
Thank you.
hi there jewellove, try:
Formula:Please Login or Register to view this content.
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
I changed the materials to Material and it is now showing data. The problem is that it is supposed to list 3 items (1-Jan, 2-Jan and 3-Aug). Why it is showing only 1-Jan??
Try it this way:
Formula:Please Login or Register to view this content.
Array Enter (Cntrl+Shift+Enter,not just Enter) and drag down
Hope this helps
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
Hi,
Look at the attached Pivot Table to see if it can give you the answer you need.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Hi Beni,
Thank you for the formula. It lists down all the items. I changed "materials" to "Material" as well...
Try it like this...
Array entered** in C7 and copied down until you get blanks:
=IFERROR(INDEX(INPUT!C:C;SMALL(IF(INPUT!$A$4:$A$100="x";IF(INPUT!$B$4:$B$100="materials";ROW(INPUT!B$4:B$100)));ROWS(C$7:C7)));"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks