Need help creating a database that will automatically update.

[ava_h]

I'm currently working on a database that will contain employee information. I would like to be able to update the master sheet, but have corresponding sheets for each one of our six locations. That way if I put in a new employee and assign them to LOC 1 (on the master sheet), they will automatically show up on the sheet I've named LOC 1. I know I can use the autofilter to show the specific locations and the employees at that location on the master sheet, but it would stiIll be more convenient to have corresponding sheets for each location. I've researched several sites, but most suggest macros and I end up feeling bewildered.

 

[sskhaliq]

HI Derek,

This seems to work for me too, however probably requires a slight adjusting of the formula. I'm working on a "working sheet" and a "master sheet".

The working sheet is supposed to fetch figures for various income / expense lines from the master sheet and populate these, quarter on quarter, in the working sheet itself.

For it to do that, my formula requires the row numbers (using INDIRECT function)as opposed to instance / serial number from 1 onwards, as is the case in the solution given by you.


In the "working sheet", I'm using

=IFERROR(MATCH($B$5,OFFSET('Master Sheet'!$B$4,0,0,COUNTA('Master Sheet'!$B:$B),1),0),"")

to fetch instance number for the first line, and then

=IFERROR(MATCH(B6,OFFSET('Master Sheet'!$B$4,A5,0,COUNTA('Master Sheet'!$B:$B)-A5,1),0)+A5,"")

for the subsequent lines. Also, the latter formula returns a series of blank cells after it encounters the first blank cell.

Would really appreciate if you could suggest a solution for this, have spent all morning and got here so far! Not very handy with Excel, as yet.

----------------------------------------------------------------------
Working sheet: http://www.flickr.com/photos/8886522@N05/5387151088/in/photostream/

Master sheet:
http://www.flickr.com/photos/8886522@N05/5387151082/in/photostream/

-----------------------------------------------------------------------

 

[Derek Brown]

I have only had time for a quick look at the formula that you are using and my guess is that you have missed changing the reference to cells in Column A when placing the formula in Column B?
Try the following:
=IFERROR(MATCH(B6,OFFSET('Master Sheet'!$B$4,B5,0,COUNTA('Master Sheet'!$B:$B)-B5,1),0)+B5,"")
Let me know if that is not the answer and I will look into it further.

 

[sskhaliq]

Hi Derek,
Sorry about the confusion. Formulae are actually pasted into column A in Working Sheet, and referenced to column B in Mater Sheet.

It works fine except for the two minor problems mentioned below ie;

1- It returns a simple count of the line description everytime it encounters it; i need it to return the row number in the master sheet of that line description.
2- It returns #N/A when it encounters the first blank cell.

Thanks!

 

[Derek Brown]

It is very difficult to understand the cause of the problem without actually seeing the layout of your data. Is it possible that you could post a section of your worksheets (desensitize data where necessary) so I can then create a test version myself? I can then use the formulas to try to work out where the error is.
If you are not familiar with posting snapshots of worksheets, have a look at:
http://www.excel-jeanie-html.de/index.php?f=1