Re: Table addressing

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi

Place your cursor within the table>Data>Pivot Table>Finish
On the PT skeleton that appears on a new page
Drag Product Name from the field list to the Page area
Drag Source to the Field area (it will appear below Product Name)
Drag Price to the Data Area
Drag Weight to the Data Area
Drag the Data button to the Total area, and the values will appear side by side.

If you do want your Product Name and Source to be side by side, and not under each other, then
right click on any cell within the PT>Table Options>Page Layout>Over then Down

If your data appears as Count of, rather than Sum Of>right click on PT>Wizard>Layout>double click on filed name>Select Sum

For more help take a look at
http://www.datapigtechnologies.com/ExcelMain.htm
and
http://www.edferrero.com/Excel
Tutorials/PivotTableTutorial2003/tabbed/89/Default.aspx
and
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/tiptech.html
scroll to section on pivot tables


--
Regards
Roger Govier

"Prof Wonmug" <wonmug@xxxxx> wrote in message news:m4um05p00g7kb2evrd9hh3sa7hj42pvm11@xxxxxxxxxx
I have a table in Sheet1 containing various specs for a list of
products:

Sheet1:

A B C D E
1 Product Name Price Weight Rating Source
2 Product A $4.55 1.00 kg 47 Company X
3 Product B $10.20 3.50 kg 87 Company Y
4 Product C $8.10 2.25 kg 61 Company Z

On another sheet, I would like to use this information and I would
like to refer to it symbolically by product name and field.

For example, the other sheet might look like this:

Sheet2:

A B C D
1 Product: Product B Source: Company Y
2
3 Weight Cost
4 1.00 kg $2.92
5 2.00 kg $5.83
6 10.00 kg $29.14

In cell B1, I would like some symbolic reference that includes the
product name (Product B).

In cells A4-A6, I would like a symbolic reference that involves the
field name (Weight), but does NOT involve the product name. I would
like to get the row in the reference sheet (Sheet1) from the product
reference cell (B1) in Sheet2.

I would like to reference the product only once in Sheet2 and then use
that to reference any of the fields for that product so that if I
change B1, A4-A6 change as well to reflect the new product.

I am sure this is possible, but I can't figure out a good way to go
about it. I would appreciate suggestions for which Excel functions or
features I should study.

.

Relevant Pages

  • Re: copying formulas
    ... I want to be able to drag the formula from B1 horizontally and have ... it automatically reference cells vertically. ... Gord Dibben MS Excel MVP ... formula references a cell in a separate column. ...
    (microsoft.public.excel.misc)
  • Re: Referencing rows to columns
    ... Enter this in any cell and drag down to copy, and this will copy Row 4 ... when copying a formula along the y axis (via the above ... naturally makes changes such that a reference to A4 becomes A5, A6, ...
    (microsoft.public.excel.misc)
  • Re: Add formula to a column of numbers
    ... Dave Peterson wrote: ... and drag down that column. ... Excel will adjust that A1 reference to point at the cell in the same row. ...
    (microsoft.public.excel.misc)
  • Referencing rows to columns
    ... by clicking the box until the + sign shows and dragging downward to ... when copying a formula along the y axis (via the above ... naturally makes changes such that a reference to A4 becomes A5, A6, ... etc as I copy the formula vertically using this same "click and drag" ...
    (microsoft.public.excel.misc)
  • Re: formula series (newbie)
    ... I just want to drag and fill the reference formula down from row 1 to row 3. ... drag and fill I'll ref the cell in sheet 2 manually. ... In are the ref cells in sheet2 that I've entered the formula manually ...
    (microsoft.public.excel.worksheet.functions)