Formula between sheets.

[AB Lighting]

I am trying to find an easy way to take a full spreadsheet and using
different sheets write a formula to take certain inflormation into a new
sheet a re sort it into different orders.

eg. I have colums of data in this case the colums are bassed on electrical
calculatios, weights. and other relevent information. Then each row is a
seperate piece of equipment that has had the information entered.

I now want to create a new sheet, in this I want to take the Information
from colomn Q then H , N and O from the first sheet I want to re sort this
information In lowest to highest from column Q with extra formula eg totals
added on the end. I need it to be able to carry the details from the original
sheet so If I were to change the No. in row Q in the first sheet then it will
automatically move all the data in that row in this new sheet.

Can anyone please give me some guidline of how to acheive this. What sort of
formula do I need to write to keep this all to tally.

I would really appreciate any help

Cheers

Andy

 

[Sheeloo]

There may be many ways this can be done but I do it using VLOOKUP

I will reorder the main sheet so that Col Q comes before Col H, N and O
I will take a new sheet then
I will copy Col Q in the main sheet to Col A of the new sheet
Then I will lable Col B-D with labels in H, N and O columns in the main sheet
Then in B-D enter VLOOKUP so that I get values from the main sheet
Once you have that you can sort Col A and still get the correct values from
the main sheet in other columns.

 

[Sheeloo]

If you need help with VLOOKUP visit
http://www.experiglot.com/2007/12/2...-part-ii-some-tips-and-advanced-excel-tricks/

 

[Roger Govier]

Hi

Try
Copy column Q form Sheet 1 to Column A of Sheet2.
I am assuming that row 1 is a header row, and that there are 1000 rows of
data on sheet1
On sheet2 enter the headers from columns H, N and O from Sheet1
In cell B2 of Sheet2 enter
=INDEX(Sheet1!$H$1:$Q$1000,
MATCH($A2,Sheet1!$Q$1:$Q$1000,0),
MATCH(B$1,Sheet1!$H$1:$Q$1,0))

Copy across through C22
Copy B22 down for the extent of your values in column A

Provided you select the whole set of data on sheet2 whenever you carry out
your sort, then the data from Sheet1 will be correctly picked up.