Copy info from one sheet to another

[dsschaser]

I have some data on one sheet that I would like copied to another that is not exactly the same (extra columns) and I'm sure how to do this. The sheet that I am copying to has additional columns in between some of the data that has calculation that ensentially summarizes the data. I have tried advance filtering which does a great job to organize the data but falls down when the headings (the extra colums) aren't the same as the source data. I would love to use pivot tables as this makes it easy to drop in data and automatically update the report but at this point I am open to anything at this point...Any sugestions? I have attached an example

BTW if this is the wrong spot for this I apologize and please direct me to the correct area.

[Cutter]

Hello dsschaser. Welcome to the forum.

You can do it with INDEX() MATCH() formula

In cell B3 place this formula:

=INDEX('Data Sheet'!$B$3:$E$6,MATCH($A3,'Data Sheet'!$A$3:$A$6,0),MATCH(B$1,'Data Sheet'!$B$1:$E$1,0))

copy it to other green cells in B,C,D and F cols

BTW - the SUM() function is used for addition so your formula in E3: =SUM((D3-C3)/C3) has unnecessary SUM() - it should be =(D3-C3)/C3
Same for G3 - change from =SUM(F3*$K$3) to =F3*$K$3 and in H3 change from =SUM((G3-D3)/D3) to =(G3-D3)/D3

[dsschaser]

That's great, thanks! Another question, what change will I need to make to accomodate more data or more sales in this case? In other words, my "Data Sheet" continues to grow, within the same format of course, by adding more entries down the sheet. Ultimately my plan will be to drop my info (raw data) into the "Data Sheet" so my "Report" is automatically generated. Can this change be accomodated? And if a limit is needed lets say to line 300....I plan to be very busy.

[Cutter]

Sorry, I got busy with non-Excel things.

I have amended your sample file. It has amended formulas from row 3 to 302. You only need to input Customer # in col A and the other columns will fill in for you.

Is that what you wanted?

[dsschaser]

Thanks, I appreciate the help.

[Cutter]

You're welcome - don't forget to mark your thread SOLVED (click on FAQ at top of page if you need directions).