Taking 12 sheets making one summary

I have 12 spreadsheets, one for each month that has revenue an
employee.  Each sheet/month has 350 or so people and the rev, however
Not all the months have the same people and rev. How do I search the 1
sheets for the names and add all the rev?  Basiclly I want to make 
summary sheet...

Good things is all names are unique...

on all sheets the names start on a1 and the rev starts on b1.

Thanks..

--
Message posted from http://www.ExcelForum.com

0
1/18/2004 4:43:44 AM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
303 Views

Similar Articles

[PageSpeed] 26

Paste the data from the 12 sheets onto a new sheet.
Do it so all data is stacked in the same columns.
Sort by name.
Use Data | Subtotal to sum rev for each employee.

Jim Cone
San Francisco, CA

"chunkylover57 >" <<chunkylover57.107niu@excelforum-nospam.com> wrote in message
news:chunkylover57.107niu@excelforum-nospam.com...
> I have 12 spreadsheets, one for each month that has revenue and
> employee.  Each sheet/month has 350 or so people and the rev, however,
> Not all the months have the same people and rev. How do I search the 12
> sheets for the names and add all the rev?  Basiclly I want to make a
> summary sheet...
> Good things is all names are unique...
> on all sheets the names start on a1 and the rev starts on b1.
> Thanks...


0
jim.coneXXX (771)
1/18/2004 6:15:09 AM
You can use the following to summarise the data, and then just throw it into a
Pivot Table.  Put the code in a module and run it, and it will create summary
sheet with all your data on it.

Assumes your data is in Cols A&B on each sheet, and currently assumes that you
have a header row on each sheet, with for example "NAME" in A1 and "REVENUE" in
B1.  If you don't have a header row on each of the sheets, and it is just the
raw data, then change the line that says StRow = 2 to StRow = 1.

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

Sub SummaryData()

Dim wks As Worksheet
Dim sd As Worksheet
Dim sht As Long
Dim lrow1 As Long
Dim lrow2 As Long
Dim StRow As Long

StRow = 2

On Error Resume Next
Application.DisplayAlerts = False
Sheets("Summary Sheet").Delete
Application.DisplayAlerts = False
On Error GoTo 0

Set wks = Worksheets.Add

With wks
   .Move Before:=Sheets(1)
   .Name = "Summary Sheet"
   .Range("A1:C1").Value = Array("Sheet", "Name", "Revenue")
End With


For sht = 2 To ActiveWorkbook.Sheets.Count
   Set sd = Sheets(sht)
   lrow1 = wks.Cells(Rows.Count, "B").End(xlUp).Row
   lrow2 = sd.Cells(Rows.Count, "B").End(xlUp).Row
   sd.Activate
   sd.Range(Cells(StRow, 1), Cells(lrow2, 2)).Copy wks.Cells(lrow1 + 1, 2)
   wks.Cells(lrow1 + 1, 1).Resize(lrow2 - (StRow - 1), 1).Value = sd.Name
Next sht

wks.Activate

End Sub

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

For a quick and easy intro to Pivot tables, see here:-

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"chunkylover57 >" <<chunkylover57.107niu@excelforum-nospam.com> wrote in message
news:chunkylover57.107niu@excelforum-nospam.com...
> I have 12 spreadsheets, one for each month that has revenue and
> employee.  Each sheet/month has 350 or so people and the rev, however,
> Not all the months have the same people and rev. How do I search the 12
> sheets for the names and add all the rev?  Basiclly I want to make a
> summary sheet...
>
> Good things is all names are unique...
>
> on all sheets the names start on a1 and the rev starts on b1.
>
> Thanks...
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.562 / Virus Database: 354 - Release Date: 16/01/2004


0
ken.wright (2489)
1/18/2004 12:23:11 PM
My summary sheet is as follows

Emp Name, Jan, Feb, March, ....  (each is a separte column)

I have to do this for several depts, so I am trying to avoid the Cut
and paste methods...Also this will be handed out...

Can I use the Vlookup and or match?

Thanks for the help

Although each name is unique, there are several smiths...

ie smithR
smithRi
smithA

etc...


---
Message posted from http://www.ExcelForum.com/

0
1/18/2004 1:58:47 PM
Once you have all the names in your summary sheet (use Ken's code to build it if
you need to), you can use a bunch (a big bunch) of =vlookup()'s.

In general, your =vlookup() will look like this (assuming the names of the
"detail" sheets are Jan-Dec).

=VLOOKUP($A2,'Jan'!$A:$B,2,FALSE)
through
=VLOOKUP($A2,'Dec'!$A:$B,2,FALSE)

(the single quotes aren't required, but will be if your worksheet name is
special--has spaces, consists of only digits....)

But if the value isn't found on a detail sheet (Jan-Dec), then you'll get #n/a's
back.

You can hide them by adjusting your formula:
=IF(ISERROR(VLOOKUP($A2,'3'!$A:$B,2,FALSE)),0,VLOOKUP($A2,'3'!$A:$B,2,FALSE))


=======

But you may want to use Ken's program to create the summary.

If you sheet names are nice abbreviation of months, you can use that and build a
nice pivottable that does the work for you.

I modified Ken's code slightly to actually put a date in column A of the
intermediate summary worksheet.  This makes the pivottable easier to sort (April
comes before January in an alphabetic sort).

Option Explicit

Sub SummaryData()

Dim wks As Worksheet
Dim sd As Worksheet
Dim sht As Long
Dim lrow1 As Long
Dim lrow2 As Long
Dim StRow As Long
Dim testStr As String

StRow = 2

On Error Resume Next
Application.DisplayAlerts = False
Sheets("Summary Sheet").Delete
Application.DisplayAlerts = False
On Error GoTo 0

Set wks = Worksheets.Add

With wks
   .Move Before:=Sheets(1)
   .Name = "Summary Sheet"
   .Range("A1:C1").Value = Array("Sheet", "Name", "Revenue")
End With


For sht = 2 To ActiveWorkbook.Sheets.Count
    Set sd = Sheets(sht)
    lrow1 = wks.Cells(Rows.Count, "B").End(xlUp).Row
    lrow2 = sd.Cells(Rows.Count, "B").End(xlUp).Row
    sd.Activate
    sd.Range(Cells(StRow, 1), Cells(lrow2, 2)).Copy wks.Cells(lrow1 + 1, 2)
    
    testStr = sd.Name & " 1, 2004"
    If IsDate(CDate(testStr)) Then
        testStr = CDate(testStr)
    Else
        testStr = "'" & sd.Name
    End If
   
   wks.Cells(lrow1 + 1, 1).Resize(lrow2 - (StRow - 1), 1).Value = testStr
Next sht

wks.Columns(1).NumberFormat = "mmm"

wks.Activate

End Sub

========
After you do this, try building the pivottable manually.

Select your range A1:C9999 (or as far as you need).
Data|Pivottable
Follow the wizard until you get to a step with a Layout button
click that button
Drag the Sheet button to the column field
drag the Name button to the row field
drag the revenue button to the data field
(if it doesn't say Sum of revenue, double click on it and change it to sum.)

The finish it up.

If you like this idea, record a macro when you do it manually and add it to
Ken's code.

If you need help tweaking the code, post back with your problem (in plain
text--no workbooks) and I'm sure you'll get help.



"chunkylover57 <" wrote:
> 
> My summary sheet is as follows
> 
> Emp Name, Jan, Feb, March, ....  (each is a separte column)
> 
> I have to do this for several depts, so I am trying to avoid the Cut
> and paste methods...Also this will be handed out...
> 
> Can I use the Vlookup and or match?
> 
> Thanks for the help
> 
> Although each name is unique, there are several smiths...
> 
> ie smithR
> smithRi
> smithA
> 
> etc...
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/18/2004 3:19:58 PM
One way

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"A
ug";"Sep";"Oct";"Nov";"Dec"}&"'!A2:A350"),"smithA",INDIRECT("'"&{"Jan";"Feb"
;"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}&"'!B2:B350")))

where it would check for smithA in A and sum it up in B. Of course this
means that you have to have some sort of system
in the layout so that all names are in the same range address..
Also note that for better usability replace "smithA" with a cell reference
on the summary sheet where you put your condition. e.g.

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"A
ug";"Sep";"Oct";"Nov";"Dec"}&"'!A2:A350"),B2,INDIRECT("'"&{"Jan";"Feb";"Mar"
;"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}&"'!B2:B350")))

where B2 would hold the name
-- 

Regards,

Peo Sjoblom

"chunkylover57 >" <<chunkylover57.108d7x@excelforum-nospam.com> wrote in
message news:chunkylover57.108d7x@excelforum-nospam.com...
> My summary sheet is as follows
>
> Emp Name, Jan, Feb, March, ....  (each is a separte column)
>
> I have to do this for several depts, so I am trying to avoid the Cut
> and paste methods...Also this will be handed out...
>
> Can I use the Vlookup and or match?
>
> Thanks for the help
>
> Although each name is unique, there are several smiths...
>
> ie smithR
> smithRi
> smithA
>
> etc...
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
terre081 (3244)
1/18/2004 3:28:34 PM
Thanks, code works great...

Couple of quick questions...

1.  The 12 spreadsheets come in as jan.xls, feb.xls, etc, is there a
easy to code or merge all 12 into new new doc that has the orginal 12
xls into 1 master with 12 sheets?

2.  Is there a way for the above code to ignore sheet "pivot"


---
Message posted from http://www.ExcelForum.com/

0
1/19/2004 2:38:35 AM
Something like this might work for you.

I prepended the path (myPath) to each filename.  I also added ".xls" (saves a
little typing).

Option Explicit
Sub combineAll()

    Dim myWorkbookNames As Variant
    Dim myPath As String
    Dim iCtr As Long
    Dim AllWkbk As Workbook
    Dim tempWkbk As Workbook
    Dim tempWkbkName As String
    Dim wks As Worksheet
    
    myWorkbookNames = Array("jan", "feb", "mar", "apr", "may", "jun", _
                            "jul", "aug", "sep", "oct", "nov", "dec")
                            
    myPath = "c:\my documents\excel\"
    If Right(myPath, 1) <> "\" Then
        myPath = myPath & "\"
    End If
    
    Set AllWkbk = Workbooks.Add(1)
    ActiveSheet.Name = "deletemelater"
    
    For iCtr = LBound(myWorkbookNames) To UBound(myWorkbookNames)
        tempWkbkName = myPath & myWorkbookNames(iCtr) & ".xls"
        If Dir(tempWkbkName) = "" Then
            MsgBox tempWkbkName & " doesn't exist!"
        Else
            Set tempWkbk = Workbooks.Open(tempWkbkName)
            For Each wks In tempWkbk.Worksheets
                If LCase(wks.Name) = LCase("pivot") Then
                    'do nothing
                Else
                    wks.Copy _
                        after:=AllWkbk.Worksheets(AllWkbk.Worksheets.Count)
                End If
            Next wks
            tempWkbk.Close savechanges:=False
        End If
    Next iCtr
    
    If AllWkbk.Worksheets.Count > 1 Then
        Application.DisplayAlerts = False
        AllWkbk.Worksheets("deletemelater").Delete
        Application.DisplayAlerts = True
    Else
        AllWkbk.Close savechanges:=False
        MsgBox "nothing imported!"
    End If
    
End Sub

"chunkylover57 <" wrote:
> 
> Thanks, code works great...
> 
> Couple of quick questions...
> 
> 1.  The 12 spreadsheets come in as jan.xls, feb.xls, etc, is there a
> easy to code or merge all 12 into new new doc that has the orginal 12
> xls into 1 master with 12 sheets?
> 
> 2.  Is there a way for the above code to ignore sheet "pivot"
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/20/2004 12:49:43 AM
Reply:

Similar Artilces:

Hide a sheet
Is there a way of hiding a worksheet and only allowing it to be unhidden if a password is supplied? Or is there any other way of achieving this? Thanks for any help! Gavin I do believe that this might do the very thing I need - I'll test it first thing in the morning! http://www.vbaexpress.com/kb/getarticle.php?kb_id=380 "gavin" <no-one@home.co.uk> wrote in message news:dg9md0$g2l$1@newsg4.svr.pol.co.uk... > Is there a way of hiding a worksheet and only allowing it to be unhidden if > a password is supplied? Or is there any other way of achieving this? &...

Copying only report sheets
Hi: Happy new year. I created a MS excel workbook which has 4 data sheets and 4 report sheets. Each report sheet links to each data sheet. Now I want to create another workbook that will show only the 4 report sheets. How can I do it in VB code? Agin, in each report sheet, I copy and paste data from data sheet and thus each report points to individual data sheet. Workbookname: ctces sheet names:dat1,rep1,dat2,rep2,dat3,rep3,dat4,rep4 Best wishes and thanks -- jesmin ------------------------------------------------------------------------ jesmin's Profile: http://www.excelforum.com/me...

Publisher 2007 Can i make a calender with holidays already on it?
IS there a way to make a calender in Publisher 2007 that already has holidays in it? I have a no frills 2009 calendar on my website with the holidays. http://msauer.mvps.org/publisher_projects2.htm Scroll down a little. Adding holidays is a manual operation in Publisher. -- Mary Sauer http://msauer.mvps.org/ "Jaci" <Jaci@discussions.microsoft.com> wrote in message news:1BB20065-41A5-479F-85E1-C4E91112ECBD@microsoft.com... > IS there a way to make a calender in Publisher 2007 that already has holidays > in it? ...

Making reminder alarm ring?
Am using Outlook 2003, with SP1. I can set reminders to make an alarm sound (like a harp), when a reminder comes due (it's in Tools > Options > Other > Advanced > Reminder Options > [X]Play sound). But Outlook has to be up and running, else no alarm sound. Is there a way to set Outlook to be sort of in the background (not just minimized, but really in the background) enough that when a reminder comes due, the alarm will sound, and either the reminder window pops up or an icon appears in bottom-right task bar? (Seems like there IS a way for it to check *e-mails* in t...

Help creating two listings from one record
I have extracted "members" from my database. One record will contain LastName and FirstName, and may also contain SpouseName. Because I am creating membership cards in Publisher, I think I probably should actually create another query or ?? that contains one record with either [FirstName LastName] or [SpouseName LastName] Probablyl something like ... If SpouseName greater spaces List SpouseName LastName else next sentence. List FirstName LastName I don't have a clue how to do this in Access. Appreciate all help. Try this -- [FirstName] &a...

Make (save) a file by using macro recorder.
Hi all, I am new to macro and using macro recoder to create macro. On one file which I recieve every week from material department. I need to reformat and create pivot table. I use macro recorder and everything is working good. However I need that when my recorded macro end it create a file with the same name with the date and time it was create. I need every week a new file and I dont want to replace the one. Please let me know how I can create a new file with the date and time everyweek when I run my macro. Thanks -- Farhan Hi Farhan This should do it, just insert you...

make private appts visible to delegate?
User has an admin as a delegate, with full edit permissions across all Outlook functions (incl Calendar), and has "Delegate can see my private items" box already checked. User's delegate, however, can only see the private items time blocked out in the calendar, and cannot see any of the details. Any sage advice or wise counsel?? -- Ctrl-Alt-Del signing off Version of Outlook? There is an issue with outlook 2007 where the private appointments are not visible to outlook 2003 users. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ ...

Which one first?Install Windows Server 2003 SP1 or Exchange SP2 ? #2
Hi, We are running Exchange 2003 on Windows Server 2003. We planned to update the software in terms of patches, so which one should I do first, the OS patches (SP1 and onwards) or the Exchange patches ? By the way, any issue with Exchange SP2 ? Rgrds, Zul See http://go.microsoft.com/fwlink/?linkid=37488 and http://support.microsoft.com/?kbid=896742. I would recomment upgrading Exchange first. -- Regards, Roland "Zul J" wrote: > Hi, > > We are running Exchange 2003 on Windows Server 2003. We planned to update > the software in terms of patches, so which one...

Chart Title or Text Box Linked to cell & sheet
I need to create charts from data collected via a com port. As i stands, the data is written to sheet 1. Chart 1 is ready to plot dat as it is written. Once the data is complete, I copy sheet 1 and char 1. This results in sheet 1(2) and Chart 1(2). All references to shee 1 on the newly created chart 1(2) automatically changes to refer t sheet 1(2). This applies to data ranges, Chart Title, Axis names. When creating text boxes refering to sheet1!$A$1, the reference doe not automacially change to sheet 1(2)!$A$1. Can this be done? If not, can I create multiple chart titles? Any help o...

Relating one column to another
Stupid newbie question coming up: Let's say that in column A I have a series (not sequential) of numbers. In column B I have a word that goes with that number. In column C, I have numbers, which represent the same words as the number in column a represents the word in column B. In other words, I have: Number Word Type: 1: Banana 2 2: Fruit 3: Sausage 4 4: Meat I want to end up with Number Word Type: 1: Banana Fruit 2: Fruit 3: Sausage Meat 4: M...

I'm look for a Visitors Log Sheet
I'm look for a Visitors Log Sheet ...

Set One Field based On Another Field
I think I have a pretty simple request. I am looking for some script to put on an on exit field. Here is what I need it to do: When a value is put in say $550, if this is greater than a specified limit in the code I need it to set Field2 to Yes. So if field1 > 500 set field2 = yes Real world example: I have a dollar amount of credit to give to a customer, I want to auto populate the approval required field with a yes. Your help is appreciated. Brent, Try: IIf([Field1]>500,[Field2]="Yes") However if the field is a Bit data type then IIf([Field1]&...

Incorporating detail of one sheet into another.
There are 2 excel worksheet. I would like to incorporate detail of sheet 2 into sheet1. e.g Sheet2 Contain the following details A B C D E F G Agent1 Agent2 Agent3 Agent4 Agent5 1 Apple Cycle 2 Mango Car 3 Banana Bus 4 Coconut Motor and so on in coloumn c , d , e,f and g. Now what I would like to do is If in sheet 1 If I select Agent 1 all the detail of sheet2 which has column as agent1 should appear exactly as it is in sheet2. And the same thing for Column B, c ...

Multiple rendering after one action
Hi There, Is it possible to render many reports with one link / action? I'm looking for any programmatically approach, which allows me to automatically renrder 25 reports to PDF/XLS Thanks in advance. michlimes Hi, You could use RS as a web service to trigger whatever you want. Seee http://msdn.microsoft.com/en-us/library/ms152787.aspx for details. -- Patrice "michlimes" <michlimes@discussions.microsoft.com> a �crit dans le message de groupe de discussion : E5B78CCB-7636-4FF8-80A3-7064C09DD9DE@microsoft.com... > Hi There, > > Is it po...

comparing two spread sheets #2
In spread sheet "A" and "B" the last names are in column B and first names are in column C. I want EXCEL to tell me if the names (a name consist of a last and first name) on spread sheet "B" or in spread sheet "A" spread sheet "A" may or may not already contain the names in spread sheet "B". How do you do this is EXCEL? Hi one way: enter the following array formula (entered with CTRL+SHIFT+ENTER) in column D of spreasheet A: =IF(ISNA(MATCH(1,('sheetB'!$B$1:$B$1000=B1)*('sheetB'!$C$1:$C$1000=C1), 0)),"not in s...

Matching Columns in work sheets and copying both rows to new
I am trying to match up two different spread sheets based on one column but to copy both rows to a new sheet. eg. First sheet has the following headings: "Owner Group " "Owner CC " "Type " "Product Categorization Tier 2 " "Product Categorization Tier 3 " "Device Name " "Status " "Model Number " "Mfg. Name " "CI ID " "Old Asset ID " "Serial Number " "Region " "Country " "Site " "Building " "Floor " "...

Only one user at a time can access OWA
Using Exchange 5.5 SP3 on W2K. Only one user can log on using OWA at any one time. As soon as the first user logs out the next can then log on, but in a organisation of 1300 - bit of a problem. Previously we did have things working fine with Exchange 5.5 on WinNT but a hacking attack put paid to that system! Have run through MS trouble shooting but found no mention of this problem anywhere else. Any suggestions welcomed Regards Mike Cameron cameronm@queensbury.beds.sch.uk (Mike Cameron) wrote: >Using Exchange 5.5 SP3 on W2K. Only one user can log on using OWA at >any one time. As...

Protecting Sheets
Im trying to find a way to protect some data in some spreadsheets that we have in our company. I know you can password protect the sheet/book, but I also know that there is a crack for these passwords on the net. Also, waht we are concerned about is staff leaving & taking the data with them. While they are at the company they will need access to password so I need to come up with another way of hiding the data. What I've been looking into is some code that runs when the book is opened that uh-hides sheets with data on that are hidden by default. What I do is check the system of the...

Combo Boxes 12-16-07
Hello, I know how to create combo boxes in a form. The problem I am having is the task I need to perform has so much information that needs to be entered. In summary, I have 65 separate sites with over 260 contractors (EACH SITE HAS 4 CONTRACTORS). I want to create a form where in one combo box/drop down list you can select one of the 65 sites. Then in the next drop down list/combo box based on the previous selection it will give you only 4 contractors to choose from. Is this possible or do I need to do it the long way? Thanks in advance for your help, Dubbs Check out http://ww...

looking for a script to make a Distribution List from a text file
Does anyone have a script for making a new Distribution List from a text file, or CSV file? I created larger dist lists by using adduser. Then, once the group is created, I mail enable it. "BwiseIT" wrote: > Does anyone have a script for making a new Distribution List from a text > file, or CSV file? > > > ...

Taking ownership of HD with different OS
My boyfriend just bought a new pc running Vista and needs to get files off of the old HD with XP Pro. He's installed the old HD (drive J) into the new pc - when he attempts to explore drive J he gets the access is denied message. I told him about taking ownership and disabling simple file sharing, but he's having a problem because the Vista interface is completely different...is the procedure a different one if the hard drives have different OS's? Try run,type: diskmgmt.msc In msc,L.click on the xp hd,go to actions, all,locate and select "make active",i...

Print 12/1/1 in cell (Excel changes it to date or another #
While trying to formulate a magazine order list, Excel changes the number of magazines needed (12/1/1) to a number other than that typed into the cell. What I need is the number 12 (for English), 1 (for MP3) and 1 (for Finnish) in this particular cell. How do I get it to print the actual numbers? try putting a ' infront of the numbers "Say Goodnight, Gracie" wrote: > While trying to formulate a magazine order list, Excel changes the number of > magazines needed (12/1/1) to a number other than that typed into the cell. > What I need is the number 12 (for Engli...

Multiple Unrelated Queries In One Report or Form?
Hello guys, I've been tasked with generating a report for my boss that provides an overview of our core database statistics. This includes the total number of orders placed each month and the total value of orders placed each month. I also need to provide the same information for purchase orders, quotations and discrepancy reports. Each individual query is a piece of cake on its own but how can I display all of this information either in a form or preferably in a nicely formatted report? The queries are all completely unrelated so there's no way I can somehow joi...

Selection from list on main sheet from suplemental sheet in same w
Okay I am new to excell bar drawing little spreadsheets to do my own financing. I am currently setting up a sheet and need the users to be able to select from a list of options only and not input thier own text - does that make sense? also i ahve 12 names on the list and one master with all on - would like the data entered on to a names list to be automatically entered onto the master or vice versa which ever is easiest. sorry if this sounds jumbled but i am totally lost and this has to look really hot for work! regards Kelly - England You only need to ask in one place, Kelly. :) *****...

How did I populate other cells based on infor from one cell?
Hi, Ive got a spreadsheet with staff information such as network username, first name, surname, department, job title, ethnicity, age etc... How do I (if possible), automatically populate the first name, surname, department, job title, ethnicity, age etc... by just typing in the network username? Any help is much appreciated! fkcadl Check out help on VLOOKUP(). With your data in Sheet1 Col A to Col J; in Sheet2 enter the network username in cell A1. In cell B1 enter the below formula and copy/drag towards the right.. =IF($A1<>"",VLOOKUP($A1,Shee...