Populate data based on selection from dropdown

Dear All ,

Hope everyone good ... Just a small help from Seniors

I am bringing Data from sheet named "estimation" to the profit and loss i was able to bring Sales and Project expense by Sumproduct and index help now i need to bring all invoices numbers from estimation when i select JEDDAH OR RIYADH FROM Cell "B3" it should give me the invoices number only by checking in the ESTIMATION SHEET COLUMN "J" Like when i select jeddah it should show me these invoices starting from B8 to downward i have put there manually for now but i want this to obtain automatically from ESTIMATION SHEET.
Highly appreciate your help
Thank you
Regards

Solution presented:

Formula:

Please Login or Register  to view this content.

Originally Posted by fslrulez

... Just a small help from Seniors ...

Makes me feel old ...

In problems like these I like to set up a formula in the main data sheet to identify the records which match the criteria, and then bring those records across to the summary sheet. So, in the Estimation sheet you can use this formula in cell R5:

=IF(J5='Profit Loss'!$B$3,MAX(R$4:R4)+1,"-")

Copy this down the column as far as you like, so that you can add more data as necessary - the hyphens will show where the formula is active.

Then in the PROFIT LOSS sheet you can use this formula in B8:

=IF(ROWS($1:1)>MAX(Estimation!$R:$R),"",INDEX(Estimation!B:B,MATCH(ROWS($1:1),Estimation!$R:$R,0)))

Copy this across to E8, and then copy those formulae down as far as you need to (until you get blanks).

I'm not sure, but you may need to use semicolons ( ; ) instead of commas ( , ) in the formulae, depending on your regional settings.

Then you can just change the drop-down in B3:I5 and the display will adjust automatically.

Hope this helps.

Pete

Another possibility is with Power Query, an Excel tool that you will need to install in Excel 2010, but worth it!
In this solution you don't need to worry with helper column or the size of the table, Power Query will adjust if for you and will show no formulas, just data!

How to Install Power Query in Excel 2010 or 2013 for Windows

Note: Cells D1:D2 can't be changed.

Delete this, pls.

Thank you so much everyone good to see alot of people coming for help <3
@pete uk sorry brother was'nt meant to make you feel old i put senior in term of respect having more knowledge then me and thank you trochan and dejunqueira

You're welcome - thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete

Originally Posted by fslrulez

Thank you so much everyone good to see alot of people coming for help <3
@pete uk sorry brother was'nt meant to make you feel old i put senior in term of respect having more knowledge then me and thank you trochan and dejunqueira

Happy to help and good luck.

brother i have 2 question in your formula if i may ask

{=IF(ISERR(INDEX(Estimation!$I$5:$I$35,AGGREGATE(15,6,(1/($B$3=Estimation!$J$5:$J$35))*(ROW(Estimation!$I$5:$I$35)-4),ROW(D1)))),"",INDEX(Estimation!$I$5:$I$35,AGGREGATE(15,6,(1/($B$3=Estimation!$J$5:$J$35))*(ROW(Estimation!$I$5:$I$35)-4),ROW(A1))))}

Q1 - In this formula what does the last part do ,ROW(A1)))
2ND- The starting point Curly bracket which represent ARRAY i tried so hard but never understood the depth of this array meaning that when we have to use that because if you use the formula without array it will still work but you put it anyway ... please can you help me learn why it was important to be there {

Originally Posted by fslrulez

brother i have 2 question in your formula if i may ask
{=IF(ISERR(INDEX(Estimation!$I$5:$I$35,AGGREGATE(15,6,(1/($B$3=Estimation!$J$5:$J$35))*(ROW(Estimation!$I$5:$I$35)-4),ROW(D1)))),"",INDEX(Estimation!$I$5:$I$35,AGGREGATE(15,6,(1/($B$3=Estimation!$J$5:$J$35))*(ROW(Estimation!$I$5:$I$35)-4),ROW(A1))))}
Q1 - In this formula what does the last part do ,ROW(A1)))
2ND- The starting point Curly bracket which represent ARRAY i tried so hard but never understood the depth of this array meaning that when we have to use that because if you use the formula without array it will still work but you put it anyway ... please can you help me learn why it was important to be there {

Q1 - I creates a sequence of numbers from 1 to 'n' lines as you go down with the formula, it helps AGGREGATE() to select the first line, the second and so on.

2nd - I didn't use the brackets '{}', may be Excel did to import to your version. The new Excel 365 is a great playground for array formulas, we can do many calculations using just one cell, very fun! And we don't need to use brackets.
You normally use it for a formula to have the array behavior, I never liked it very much, it expands the result of a (cell) formula to some extent in latter versions, not as good as now.

If possible try to upgrade to Excel 365, many new functions to work with big data.