Find populated cells in list of products and their ingredients and weights

Post an example file, and in it explain clearly what you want to achieve.

Pete

Thank you!

Originally Posted by alansidman

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

We have replied to this title request change. Thus please note that the request has been fulfilled!

I suggest you send a PM to Alan to ask him to delete his post.

As regards your example, please state the columns that you use in your real data (500 columns is a bit difficult to work out the column identifiers).

Pete

Hi Pete,

I hope this helps a bit. I've attached an example with data that is closer to the real data we use. I'm not sure what you meant by "please state the columns that you use in your real data" but perhaps this updated example helps.

Let me know if you are looking for different or further clarification. And thank you so much for the help!

-Andrew

P.S. Alan thank you for deleting your post.

Originally Posted by ChemistB

Here's a solution (I think). I made two dynamic named ranges (they will change size as you add products). "Products" which would be your list of products in column A of sheet1 and "Ingredients" which would cover the range from X4:WS? of your ingredient amounts.

Then in Sheet2, I used Data Validation to create the dropdown in A1 and used Array formulas to bring back the ingredients, units of measure and amounts. They are respectively (in B2,B3 and B4)

Formula:

Please Login or Register  to view this content.

Is this what you were looking for?

Hi chemist,

First of all thank you so much for responding. It looks like you have solved this problem! However, as a somewhat new user of excel, I'm pretty confused by the formula you've used and therefore I'm having trouble implementing your solution. We've tried to break your formula down and understand it, but we've had trouble. If you have a moment do you mind explaining what's going on in the super-formulas you used?

Thank you,
Mr. Euphorium