Each day, 3-10 different foods (from a selection of 100) are fed and the
weights recorded. My spreadsheet is currently set up with the following
columns:

Date|Food1Name|Food1Amount|Food2Name|Food2Amount|F ood3Name|Food3Amount|...etc...

The way I have it set up, though, it isn't simple to get a sum of the total
amount of, for example, apples fed in a week/month/etc., because they may be
listed under "Food1" on one day, but "Food2" on another day.

I COULD have a column for each foodstuff, but that then makes data entry a
pain for users, who then have to scroll left and right to find the
(hopefully) correct column.

I've looked at pivottables, sumif, sumproduct, and vlookup-type solutions,
but none of them seem to be quite what I need or so complex that I wouldn't
be able to show others easily.

Is there a different way to approach this that would allow easy data entry
AND easy (simple) data analysis?


Thanks,
Heidi

A database program like Access would make this much easier. However, you may
want to try the Advanced Filter (Data/Filter/Advanced Filter....)

For the sake of explanation, insert 11 blank rows at the top of your
spreadsheet. Copy the header row so it appears on both row 1 and row 11. All
your data is now in rows 12 and on.
Now, the first 10 rows act as your filtering criteria (or however many you
choose, it could be 2 or 300, I just said 10 to get you started)

So, if you want to expose all rows that contain "Apples" in any of the food
columns, then you would put "Apples" in B2, D3, and F4 (see your note below I
assume a 1 for 1 column name).
Now assume you have 100 rows of data (A11 through A101)

So, your Advanced Filter Criteria would be:
List Range: A11:Z101
Criteria Range: A1:Z4

Remember that when you read the criteria across it is equivalent to "AND" if
you read the
Crtieria columns down, this is the equivanent of "OR" This is why you need
to have APPLES
on different rows for each food group. If you had it all on the same row,
this would mean all three foods choosen must all be apples.

You can combine this with the Subtotals for each column and then add the
food amount column totals for your answer. Cheesey, but it would work.

Better solution would be an Access query on the Excel data using it as a
source table.
See if that helps. If you have trouble, post back and I'll get you my
contact information.

Jim


"Heidi" wrote:

Each day, 3-10 different foods (from a selection of 100) are fed and the
weights recorded. My spreadsheet is currently set up with the following
columns:

Date|Food1Name|Food1Amount|Food2Name|Food2Amount|F ood3Name|Food3Amount|...etc...

The way I have it set up, though, it isn't simple to get a sum of the total
amount of, for example, apples fed in a week/month/etc., because they may be
listed under "Food1" on one day, but "Food2" on another day.

I COULD have a column for each foodstuff, but that then makes data entry a
pain for users, who then have to scroll left and right to find the
(hopefully) correct column.

I've looked at pivottables, sumif, sumproduct, and vlookup-type solutions,
but none of them seem to be quite what I need or so complex that I wouldn't
be able to show others easily.

Is there a different way to approach this that would allow easy data entry
AND easy (simple) data analysis?


Thanks,
Heidi

Thank you! Advanced filters seem to be a great solution (other than
purchasing Access, which isn't an option for us).

Thank you for your help,
Heidi



"Jim Aksel" wrote:

A database program like Access would make this much easier. However, you may
want to try the Advanced Filter (Data/Filter/Advanced Filter....)

For the sake of explanation, insert 11 blank rows at the top of your
spreadsheet. Copy the header row so it appears on both row 1 and row 11. All
your data is now in rows 12 and on.
Now, the first 10 rows act as your filtering criteria (or however many you
choose, it could be 2 or 300, I just said 10 to get you started)

So, if you want to expose all rows that contain "Apples" in any of the food
columns, then you would put "Apples" in B2, D3, and F4 (see your note below I
assume a 1 for 1 column name).
Now assume you have 100 rows of data (A11 through A101)

So, your Advanced Filter Criteria would be:
List Range: A11:Z101
Criteria Range: A1:Z4

Remember that when you read the criteria across it is equivalent to "AND" if
you read the
Crtieria columns down, this is the equivanent of "OR" This is why you need
to have APPLES
on different rows for each food group. If you had it all on the same row,
this would mean all three foods choosen must all be apples.

You can combine this with the Subtotals for each column and then add the
food amount column totals for your answer. Cheesey, but it would work.

Better solution would be an Access query on the Excel data using it as a
source table.
See if that helps. If you have trouble, post back and I'll get you my
contact information.

Jim


"Heidi" wrote:

Each day, 3-10 different foods (from a selection of 100) are fed and the
weights recorded. My spreadsheet is currently set up with the following
columns:

Date|Food1Name|Food1Amount|Food2Name|Food2Amount|F ood3Name|Food3Amount|...etc...

The way I have it set up, though, it isn't simple to get a sum of the total
amount of, for example, apples fed in a week/month/etc., because they may be
listed under "Food1" on one day, but "Food2" on another day.

I COULD have a column for each foodstuff, but that then makes data entry a
pain for users, who then have to scroll left and right to find the
(hopefully) correct column.

I've looked at pivottables, sumif, sumproduct, and vlookup-type solutions,
but none of them seem to be quite what I need or so complex that I wouldn't
be able to show others easily.

Is there a different way to approach this that would allow easy data entry
AND easy (simple) data analysis?


Thanks,
Heidi