Average of values that need to be looked up from another sheet

[NLJR]

I have multiple price tables that go along a worksheet that are for different
products. For each product there is a:
Low High Average
These prices are reported weekly and the week is on the first column.

I am trying to use a lookup prices so that I can average them by year,
quarter and month. I created a unique ID for each product that is found in at
the top left hand corner of each table so that I could use the offset
function to find the starting reference point but now I am stuck as to how I
can get it to perform the average once I have identified which table it is
meant to grab the information from.

Any help would be much appreciated.

 

[Shane Devenshire]

Hi,

For many operations a better data layout would be

UniqueID Date ProductDesc Low High Average

This layout would allow filtering, pivot table, sorting, all kinds of
overall, monthly, quarterly and yearly averages with relatively straight
forward formulas. Data entry might also be easier because it would be at the
bottom in one place rather than moving from table to table. A quick filter
would show you the equivalent of your current table by product.