Excel Help

[Raven]

Hi,
I have a list of items with a price which varies on size, (see excel file). I want to make a full list of the prices so that I have 3 columns. Column A = Height, Column B = Width and column C = Price

From the below table I want to see a list like this

3500 x 1000 £2334
3500 x 1250 £2394
3500 x 1500 £2455

And so on, so that it lists all variants. Obviously I could do it manually but I am wondering if there is something in excel that can do it for me and would be little fuss to update upon a price change?

Much reppage if you can help!

 

[ST^]

What version of Excel?

You should make 'width' a header instead of a footer, then start the pivot table wizard and choose "multiple consolidation ranges". When asked for the ranges, just select all of the data including the height column and the width row. When you have the pivot table, well, you probably know what to do after that

 

[Bob007]

Not sure Excel has something for that. If it did it would probebly stem from a an "offset" function (with about 40 nested "if"'s ). Only way i could come up with to minimise the work on updating, was to build it with cell refreneces from the table.

I've attached your file showing what I mean but I'll try to explain.

I copied your table on to table 2 with patse special and only pasted the refrences to original table. You could skip this but i like a clean table with no formating

Next on Sheet 3, I made the first cell refrence A1 to sheet2 and A1. this gave me the title for that column. "Hieght in MM". Then in B1 made a cell refrence to sheet 2 A23 for "Width in MM" added cost as final cell title for C1 and formated cells as needed.

Still on sheet 3, in cell A2 i made a cell refrence to sheet 2, cell A2 and copied it down 21 cells to give me all the Hieghts listed. Then i want back to top and made the cells absolute for all 21. Then i just copied and pasted these down the column 11 times. This gave me all the height parts i need.

Sheet3, cell B2. For width i just did a cell reference for sheet 2. cell B22. Made this absolute $B$22 and copied that down 21 cells to the first cell with "1000**" in it. repeat this for all the other widths. (I;ve done 3).

Sheet3, cell C2. I can't think of an easy way of doing this. I just linked to cell B2 on sheet 2 and dragged it down. Repeat for rest.

This way is a bit of work to begin with. But has the attraction of a single point of edit. Changing data in the original table will change data in the columns accordly. Only prob i see is if you get new sizes. I know its not what your after. But i couldn't think of an easier way.

I've attached the file anyway. take a look and as said at the start check hte Offset function, but i believe the complexity of the table it may not work.

 

[Raven]

Thanks both

 

[Jupitus]

I'd have offered help too, until someone mentioned the pivot table which is clearly sent to earth by satan as a tool for enlisting dead souls into his army of doomed zombies....

 

[xane]

Go to Sheet 2 and copy this formula into A1

=CONCATENATE(INDIRECT(ADDRESS((INT(ROW()/12))+3,1,1,TRUE,"Sheet1"))," x ",INDIRECT(ADDRESS(24,MOD(ROW()-1,11)+2,1,TRUE,"Sheet1"))," ",TEXT(INDIRECT(ADDRESS((INT(ROW()/12))+3,MOD(ROW()-1,11)+2,1,TRUE,"Sheet1")),"£#,###"))

Then copy to lines below.