Wanted: Excel Ninja(s)

Iceman.USAF

So, I come to you again for more Excel knowledge.

As you may or may not know, I work for an engineer squadron in the USAF. Pretty awesome. You may also know that the USAF is not the most...shall we say, coordinated entity in the construction business.

To that end, I'd like to help by creating an excel file populated by various OTHER excel files. My question to you: Where do I start? I know how you can link cells to other sheets in excel, but I need something a bit bigger. What I want to do is create one "master" excel file which draws its data from several other excel files. Is this possible? Is there a word for this type of linking?

Chanus

Would it be easier to just combine them as sheets within one file or are these multi-sheet files?

Another idea would be to use Word to make a sort of Table of Contents page that hyperlinks the other files... though I'm not really sure how much interaction you plan to have within the Excel files... is this just reference or will values in the individual files be used together?

Deebaser

edit: meh

Deebaser

What are you trying to accomplish? This sounds like Access work to me.

RUNN1NGMAN

I don't exactly have an answer, but a few tips. First, you have to master the Excel terminology. Separate files are called "workbooks." I googled "linking excel workbooks" and got this. Probably a good start, at least.

Tofystedeth

Deebaser wrote: »

What are you trying to accomplish? This sounds like Access work to me.

Yeah, this might be edging perilously close to database work.

Arikado

Hmm, would VLOOKUP be something he'd want to use?

Chanus

Arikado wrote: »

Hmm, would VLOOKUP be something he'd want to use?

If everything is static that would work... but I'm not even sure what he's trying to do in the first place.

LaOs

More information is definitely needed, especially regarding how the "source" workbooks are used, where all of the workbooks are in relation to one another, and what is wanted out of the "master" workbook.

PolloDiablo

The word for that kind of linking is a relational database. You might be able to swing it in Excel, but as far as I'm aware it's better suited to Access. Even if you could do it in Excel, it would certainly be easier with a database program, because that's what they're built for.

Corvus

I'm a middle level Office user, but I think this might be easier to do as an Access project. You can import a variety of excel sheets as tables within one Access DB.

Asiina

Like others have said, you can force it through excel with the VLOOKUP function, but creating a relational database in Access will be a lot easier in the long run.

Iceman.USAF

I'll have to check up on the Access route. To be honest, if I had ever used Access before I may have tried it. Well, I know what I'll be messing with at work today! Haha!

Anyway, to clarify...

We've got several sections all working on various parts of a given construction project. Keeping one Excel file isn't working, because people can't edit it on the fly as they'd like or if they can, someone is editing the file (so they cannot) and they forget about it until much later. We need to keep up to date data readily available.

What I'd like is to have one master workbook that draws data from several others that people can keep in their own personal workbook.

So when I open the master workbook, it grabs the pertinent data from all the other ones and populates it with the required information from the individual engineer's workbooks. Does that make more sense?

Aumni

One solution:

Open an Access database

Go to the tables section

Right click in the blank area in the window.

Choose 'Link Table'

Choose the excel files you want to use. Each Excel file will represent 1 Linked Table. You'll probably want to create one for each section, or hell, each user.

You should now have an access database that pulls information from the linked tables, which are pulling from the excel files.

When someone updates an Excel file, the changes will be reflected in the linked table in access.

You can then create access reports which go off of these linked tables. For pretty eye candy. If you or someone knows SQL/Access reports, you can use 'DLookUp' to pull information from each table into one master report. With graphs and 3d pictures and ninja stars.

1ddqd

I second the Access route, especially if you're working in the 2007 suite. It's much more integrated and user friendly, especially for a first time user.

Iceman.USAF

Thanks guys, I'm going to give the Access thing a whirl and see how it goes. I'll be back if (when) I have more brain-busters.

mspencer

As my office's Access guru, I agree with the above.

I have to ask btw: Redhorse? Or another engineering squadron?

Iceman.USAF

No, we're just a regular Prime BEEF unit. Nice to see someone who knows what the hell I'm talking about though!

mspencer

My Dad is retired USAF. He was over in England with the 819th Redhorse, and then in Florida with the 823rd for a few years.

I'm a CS grad student. While other people in this thread are probably more database-savvy than I and are just as qualified as I am to do this:
Can you ask if you're allowed to post the kinds of objects or things that you track, and what the things are that you use to describe them? Not everything unclassified is necessarily public. I can't send you an MS Access database file, but I can describe how to build what you need.