Excel - copy certain rows from worksheet in another worksheet

Question

I have found ways to solve this issue but I don't know how to tailor them to my specific problem. I've never written a marco or any VBA script before, so please be specific with your answers.

I have an Excel workbook with one worksheet named "foods", and four other worksheets named "snack choices", "breakfast choices", "lunch choices", and "dinner choices"

The heading in the "foods" worksheet looks like this:

Food Amount Calories ... ETC ... Type

Where "Type" contains "breakfast" "snack" "lunch" "dinner" or a combination of 2+ choices.

I'd like the entire row to copy to the corresponding "snack choices", "breakfast choices", "lunch choices", and "dinner choices" worksheet according to the value of the "Type" field in the "foods" worksheet.

For example, if I enter a new row into the "foods" worksheet with the "Type" field as "snack / breakfast" I'd like for the row to automatically appear in the "breakfast choices" and "snack choices" worksheets. (I could remove the slash or replace it with another character if that makes it easier)

Thank you in advance!

Answer 1

Main table with foods (Sheet 'Foods'):

Table for breakfasts:

In this table:

A1: Type of food. In order to match combinations with other foods (Lunch/Cereal/Breakfast), it should start and end with '*': *breakfast* instead of breakfast. It's possible also to make a change in formulae.

Then we look for the first occasion of "breakfast" on the sheet 'Foods':

A3:

=IF(ISERROR(MATCH($A$1,Foods!A:A;0)),"",MATCH($A$1,Foods!A:A,0))

As a result, a number of the matching row will be shown. If no match, a blank cell will be shown.

Then we match other data:

B2:

=IF(A3="","",INDIRECT("Foods!B"&$A3))

D2: change INDIRECT("Foods!B"&$A3)) to INDIRECT("Foods!D"&$A3)), E for E2 etc.

Other rows in column A below the first one (copied downwards from A4):

=IF(ISERROR(A3+MATCH($A$1,INDIRECT("Foods!A"&A3+1&":A60000"),0)),"",A3+MATCH($A$1,INDIRECT("Foods!A"&A3+1&":A60000"),0))

If no match, a blank cell will be shown.

I uploaded a spreadsheet example to http://www.bumpclub.ee/~jyri_r/Excel/Type_of_food_on_another_sheet.xls