# Super duper mega advanced excel formula.



## eagleradio (Nov 28, 2009)

Hi all,

I thought I would try posting here incase there are some insanely knowledgable Excel people who might know this one. I am using Excel 2003 at work, and I work on a minesite. We have a number of personnell lists that all contain the same basic information.

As it stands right now we have two personnel onsite lists, one is kept as a Microsoft Excel Spreadsheet, the other is put into a remotely held database program. We then also have to make up muster point lists. Everytime we have people arrive or depart the site (everyday) we have to update all 3 lists. Also there are people changing rooms, or job positions and all these changes have to be carried through and correlate on all lists.

I am currently getting the IT people (in the U.S.) to make our database program spit out an excel data table.

What I hope to achieve is the ability to write someone's name on an excel spreadsheet, and have a formula lookup the details I want (Room, Job position, company etc) from this 'master' data sheet.

Do you think this would be possible? I have a good grasp of basic excel formulas and I am capable of figuring things out to some extent but this is a country mile over my head.

If anyone has done some sort of similar data correlation please let me know how or where I can look for this as I havn't found very much so far!!

Thanks!
Gary


----------



## macropod (Apr 11, 2008)

Hi Gary,

Sounds like a job for a LOOKUP formula or a combination of INDEX and MATCH.

If you can post back some details of the structure of your 'master' sheet (eg which columns hold the person's name, Room, Job position, company etc, it should be possible to develop a suitable formula without too much difficulty.


----------



## eagleradio (Nov 28, 2009)

Wow, thanks macropod. I had been looking at the Vlookup (Go my "Excel 2003 for Dummies" book) but it is getting way out of my current abilities in Excel.

I will attach a shot of the crew sheet as well as one of the muster point. If the data can be grabbed straight off the crewlist, that would be awesome, simply because teaching the 'older boys' to use a data sheet will be painful. But if that's what needs to be done, no problem.

I am hoping to be able to simply type the name of the person on the muster list, and have it poulate the remaining data for that person from the crew list.

What do you think?


----------



## macropod (Apr 11, 2008)

Hi Gary,

Your thumbnails suggest what you really need is the ability to produce muster lists by muster point. I don't see how inputting names onto the muster sheet will achieve that. Rather, it seems you'd need formulae that retrieve the details of all employees whose muster # on the master sheet matches the muster # on the muster sheet. But you can also get muster point lists without any formulae - all you need to do is to set up an Autofilter on the master sheet, based on the heading row and filter by muster point.

Having said the above, an _array formula_ you could use for what you asked on the muster sheet is:
=INDEX(Master!D$1$1000,MATCH($A2&$B2,Master!$B$1:$B$1000&Master!$C$1:$C$1000,0))
where:
column D on the Master sheet holds the position data, columns B&C on the Master sheet hold the first name & surname data and cells A2 & B2 on the muster sheet hold the first name & surname of the person who's details you're retrieving. The formula also assumes the master sheet data spans rows 1-1000. Adjust the range references on both sheets to suit. Changing the column D reference for the Master sheet accesses the data in the other master sheet columns.

*Note*: Array formulae are entered with 'Ctrl-Shift-Enter', rather than just 'Enter'.


----------



## eagleradio (Nov 28, 2009)

OK macropod thanks a lot. I will need a bit of time figuring it out but I will post back as soon as I have an outcome.


----------



## eagleradio (Nov 28, 2009)

OK well, so far I am not getting very far by trying to back track through the formula, but I will start reading up on the "Index" formula more so I have a bit more understanding of what I am trying to use. It's not one I have ever used before so I am a little lost, but thanks macropod & I will let you know how I get on.

Gary


----------



## macropod (Apr 11, 2008)

Hi Gary,

See the attached workbook. It has three sheets:
.Master Sheet
.Name List
.Muster List

The 'Master' Sheet has 'auto filtering' applied. With this, if you click on the 'Muster Point' dropdown, you'll see that you can select any of the available muster points, or all of them. If you select '1', the filtering will hide the rows for all the other muster points. Print this and you've got the muster list for muster point 1. Simple.

The 'Name List' Sheet has the formulation you asked for. You input the names in the green cells and, if you've got more than one name, copy the formula from row 2 down as far as needed. For this to work, you obviously need to know who's allocated to a given muster point before inputting their names - sort of like having to know the answer so you can figure out what the question is.

The 'Muster List' Sheet requires you to input the muster point in the yellow cell. That's all there is to it. The formulae automatically build the list for that muster point. If you need more rows, simply copy down the formulae from any row AFTER row 2.


----------



## eagleradio (Nov 28, 2009)

Wow, that is a really great setup. Thankyou sooo much. Sorry about the late reply (it was my off time), but this is a magnificent thing you have made up. I like my chances of 'fiddling' with this a lot more than starting from scratch. I had mostly error messges anyway! I will keep working with this list you have done and get back to you when I get stuck (which I probably will).

Thanks again.


----------



## eagleradio (Nov 28, 2009)

macropod, thanks so much again, I now have a workable sheet and I have been tweaking and formatting it so it looks more like our old sheets, to help stop my workmates from totally freaking out when I try to show it to them.

I do have one question. As a result of all the formulas, changing any entries can lead to a decent lag while it recalculates. I am pretty sure that you can set it to only update calculations on command, is that possible? If not, can the pages be set in different files and referenced, so that the calculations only take place when that particular file is open?

I can see the second option causing all kinds of problems though. I'm sure the first one is possible, I remember our sheets used to only calculate on save and I had to do a bit of research to get it back to normal. Any clues appreciated.

Gary


----------



## eagleradio (Nov 28, 2009)

For anyone reading, it's Tools > Options > Calculations 

I should search BEFORE I post really.:grin:


----------

