+ Reply to Thread
Results 1 to 3 of 3

Using LOOKUP across several sheets to autofill and count

  1. #1
    Registered User
    Join Date
    06-27-2014
    Location
    South Tyneside
    MS-Off Ver
    2013
    Posts
    23

    Using LOOKUP across several sheets to autofill and count

    Hi everyone

    I've tried to complete this task with my limited knowledge and I've hit a brick wall.

    The attached document contains three sheets. Here's what I'm trying (and failing) to do:

    When entering a postcode (zip code for those outside the UK) in to Column C of the 'Participants' sheet, I want it to search the sheet titled 'Combined postcodes', Column A, and autofill in to column E in the 'Participants' sheet the ward as listed in Column C the 'Combined postcodes' sheet.

    Once this is done, I want to be able to count how often the Ward appears in the sheet 'Participants' and show it against the ward name in the sheet 'Ward totals'.

    To complicate this a little further, if a postcode entered in the 'Participants' sheet does not correspond with any postcodes in 'Combined postcodes' sheet, I want it to autofill as 'Outside area' and this would be counted against the Outside area tally on the 'Ward totals' sheet.

    I realise that this seems like a big task (well it does to me) but I'm hoping that for some of you very experienced Excel users this will be a walk in the park!

    I will really appreciate your help on this. I hope someone can do me a huge favour!

    Many thanks in advance

    Ian
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Using LOOKUP across several sheets to autofill and count

    Please try at
    Participants E2
    =IFERROR(VLOOKUP(C2,'Combined postcodes'!$A$1:$C$10592,3,),"Outside area")

    Ward totals B1
    =COUNTIF(Participants!$E:$E,A7)
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-27-2014
    Location
    South Tyneside
    MS-Off Ver
    2013
    Posts
    23

    Re: Using LOOKUP across several sheets to autofill and count

    Hi Bo_Ry

    Thanks for the help. I really do appreciate it.

    The first one worked like a charm. Thank you!

    The second one needed a tweak as I don't think I quite explained it properly. I need to enter it for each specific ward like this - =COUNTIF(Participants!$E:$E,"Southwick") so it counts the number of times the ward 'Southwick' is mentioned. But I couldn't have fathomed that without your initial suggestion. So thank you very much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula for Using Lookup for Autofill
    By TWP in forum Excel General
    Replies: 6
    Last Post: 11-06-2015, 06:18 PM
  2. [SOLVED] Lookup value then autofill entire row
    By miksel in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-11-2015, 07:48 AM
  3. Autofill base on Active Cell and Autofill by row count of another sheet
    By enyak in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-19-2014, 10:06 PM
  4. Disable lookup autofill
    By ajaykgarg in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-02-2010, 10:49 AM
  5. Autofill/lookup in macro
    By Jymoz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2009, 03:43 AM
  6. autofill+addrows+lookup
    By mhax in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-15-2007, 08:50 AM
  7. [SOLVED] vba for lookup and autofill
    By delmac in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-23-2005, 07:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1