Jobs

Computers

pivot tables

How do you create a pivot table using data over 2 sheets?
I have a spreadsheet with 42 columns containing names and flight details, however I need to see how many people are on 1 flight, however the flight details are spread over 2 excel sheets. I am ok doing a normal pivot table with everything coming form one sheet but this is really hard. If you do please treat me as an idiot when explain and I won't take a fence.

Thanks

focusjc85 Thurs 06/11/08 16:14

<< Previous

Next >>

Subscribe to question

Post an Answer

dundurn
Thurs 06/11/08
17:00

I don't think you can create a pivot table from two separate ranges of data. I'm assuming the flight details are on two separate sheets for some good reason. I'd be tempted to amalgamate the data you need on a single tab and then run the pivot table from there.

Create a new tab and set up formulae to read the data you need for your passenger data. Then use a lookup formula to bring over the flight data. You should be able to do it in one formula.

Let's say the new tab is called Newtab, and the original tabs are Pax and Fa and Fb respectively with the flight reference in column C of Newtab and column A of Fa and Fb with the data you need to bring across in column B of Fa and Fb

=IF(ISERROR(VLOOKUP(C2,Fa!A:B,2,FALSE)),IF(ISERROR(VLOOKU P(C2,Fb!A:B,2,FALSE)),"Not found",VLOOKUP(C2,Fb!A:B,2,FALSE)),VLOOKUP(C2,Fa!A:B,2,FALSE ))

In passing: do you mean 42 columns? or 42 rows? Columns would seem to be an unusual way to set up a spreadsheet like this. Surely it would be easier to put each entry under the one before? I can't imagine having 42 different things I'd want to record about each passenger but I can imagine having 42 passengers. If you've put each passenger into a separate column, then you'd have to change all the Vlookup calls to Hlookup.

Post an Answer

Submit the above question and answers

add to del.icio.us	add to digg	add to furl
add to reddit	add to Technorati	add to Blinklist
add to StumbleUpon	add to squidoo	add to ma.gnolia
add to Cocomment	add to Netscape	add to Fark