Excel worksheet consolidation

[Ronin Storm]

Here's one for the Excel literate bunch among you.

I have seven worksheets (of EVE financial data) each in separate workbooks. I want to consolidate each of these worksheets into a single worksheet in a summary workbook, which I then perform some cunning pivot table stuff to get some analysis out of the back. I will need to remove duplicates based upon a unique ID that is always present for each data row.

I can doubtless write some VBA to do this but I dislike VBA so I'd like to avoid that. Is there another way I can consolidate these sheets? I'm using Excel 2007 if that makes a difference.

 

[thatbloke]

copy and paste?

I believe you can have multiple workbooks open, I'm not sure of the syntax but if you then use the formula helper thingy where you click the box that allows you to manually select which cells are to be used in your formula, I believe you can select other workbooks as those fields. It will then fill in teh syntax for you and you should be able to do it...

 

[Traxata]

Sort of came up with something that needs to do this;

=IF(B1>=0,Sheet2!B1 AND B3=IF(B2>=0,Sheet2!B2),"")

But it also needs to actually work....

 

[Ronin Storm]

copy and paste?

That's what I'm doing now. Looking for some automation.

The trouble is that I don't know how many rows I'm dealing with in the source sheets so I can't just drop straight "copy to here" formulae in the appropriate cells cause I don't know where the appropriate cells are.

 

[thatbloke]

I'll be honest, this sounds like it might be a job for VBA.

However much you hate it...