View Single Post
 
Old 11-05-2014, 04:08 AM
JaxV JaxV is offline Windows 7 32bit Office 2010 32bit
Novice
 
Join Date: Nov 2014
Posts: 8
JaxV is on a distinguished road
Default Counting unique visitors by ward, counting monthly visits by status, editing existing workbook

Hello all

I am looking for the answer to 2 things really.

Firstly, I am attempting to make a count of visitors to a site by postcode/ward. At the moment, I have Sheet 1 column A to enter postcode. I have on another sheet (same workbook) 3 coloums of all the postcodes within the 3 wards I want to check against. In sheet 1, I have added a column B using drop down boxes to manually choose the ward after searching for the postcode in sheet 2 and noting which column this falls under.

Now, this is fine for new entries, but I have over 600 unique visitors already on the worksheet.

Is there a way to get column B to auto check against sheet 2 and auto-enter the ward based on which column the postcode is found under on sheet 2?

So, something that would like;

User manually enters postcode in sheet 1 cell A1
If postcode entered matches entry in sheet2 column A, enter "ward A" in sheet1 cell B1
If postcode entered matches entry in sheet2 column B, enter "ward B" in sheet 1 cell B1
If postcode entered matches entry in sheet2 column C, enter "ward C" in sheet 1 cell B1
If postcode entered does NOT match any entry in sheet2, enter "other" in sheet 1 cell B1

And so on from sheet1 cell A1 down to A600 and beyond for new entries.

The second issue is that I need to count monthly visitors by status. So for example, at the moment I have a row per visitor with drop down boxes for their employment status and then counts of their visits per month. So if name is in A1, employment status via drop down selection in A2, then A3 would be "april 2014" and manually enter a count of "1" if they visit in that month (or 2, or 3, depending how often they visit), A4 for March 2014 etc etc.

I can count total number of visitors that are employed, unemployed, student or retired (or info not given) but I now need to break it down by month.

So, my monthly totals sheet would need to have unemployed visitors in april, employed visitors in april, etc. Again, for an exisiting worksheet with over 600 unique visitor entries.

I know these things are possible I just cant get my head around how
Reply With Quote