Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-05-2014, 04:08 AM
JaxV JaxV is offline Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Windows 7 32bit Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Office 2010 32bit
Novice
Counting unique visitors by ward, counting monthly visits by status, editing existing workbook
 
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
  #2  
Old 11-05-2014, 07:10 AM
JaxV JaxV is offline Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Windows 7 32bit Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Office 2010 32bit
Novice
Counting unique visitors by ward, counting monthly visits by status, editing existing workbook
 
Join Date: Nov 2014
Posts: 8
JaxV is on a distinguished road
Default

Ive added a sample of the workbook with all other info removed.

You can see there are 3 sheets. Sheet 1 is for users to enter information, and column B in sheet 1 is where I would like the ward area to auto fill when the postcode is entered into column A. The postcodes to check are on sheet 2, and then the total I would like to populate are on sheet 3.

Hope it makes sense, I have gone spreadsheet blind
Attached Files
File Type: xls Registrations Sample.xls (85.5 KB, 13 views)
Reply With Quote
  #3  
Old 11-06-2014, 06:19 AM
JaxV JaxV is offline Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Windows 7 32bit Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Office 2010 32bit
Novice
Counting unique visitors by ward, counting monthly visits by status, editing existing workbook
 
Join Date: Nov 2014
Posts: 8
JaxV is on a distinguished road
Default

I think I may have a solution for the 2nd issue, but need some help still (anyone? )

I have set a conditional format to change the cell colour depending on the option selected from the drop down box (employment status). I would then like to auto copy the formatting (not the text) to the row where number of visits is entered. This way, I can then set it to count only the numbers with a specific format.

So...how can I set conditional formatting to format a row of cells based on a different cells text?

Thanks in advance, i'm sure there is someone with the answer
Reply With Quote
  #4  
Old 11-06-2014, 06:37 AM
JaxV JaxV is offline Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Windows 7 32bit Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Office 2010 32bit
Novice
Counting unique visitors by ward, counting monthly visits by status, editing existing workbook
 
Join Date: Nov 2014
Posts: 8
JaxV is on a distinguished road
Default

Ok figured that out, now I just need to work out how to have the same rule, row by row, for 600+ rows without having 600+ rules
Reply With Quote
  #5  
Old 11-06-2014, 07:02 AM
JaxV JaxV is offline Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Windows 7 32bit Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Office 2010 32bit
Novice
Counting unique visitors by ward, counting monthly visits by status, editing existing workbook
 
Join Date: Nov 2014
Posts: 8
JaxV is on a distinguished road
Default

Got it Still need help with the first issue but think I have the second sorted now
Reply With Quote
  #6  
Old 11-06-2014, 11:00 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Windows 7 64bit Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,759
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

What is the first issue please?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #7  
Old 11-06-2014, 11:14 AM
JaxV JaxV is offline Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Windows 7 32bit Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Office 2010 32bit
Novice
Counting unique visitors by ward, counting monthly visits by status, editing existing workbook
 
Join Date: Nov 2014
Posts: 8
JaxV is on a distinguished road
Default

As stated in the OP:

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.


I attached a sample in the second post.

Thanks
Reply With Quote
  #8  
Old 11-13-2014, 03:49 AM
JaxV JaxV is offline Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Windows 7 32bit Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Office 2010 32bit
Novice
Counting unique visitors by ward, counting monthly visits by status, editing existing workbook
 
Join Date: Nov 2014
Posts: 8
JaxV is on a distinguished road
Default

Still looking for help on the postcode look up, ive tried various lookup and if formulas but still a bit of a noob :/

This is what I want the formula to achieve:

Postcode is entered manually in 5C.
5D contains formula to check postcode against postcodes! columns A, B and C.
If postcode is found in column A, fill 5D with text "Ward1"
If postcode is found in column B, fill 5D with text "Ward2"
If postcode is found in column C, fill 5D with text "Ward3"
If postcode is not found, fill 5D with text "Other"

This formula would need to be copied down for 600+ rows so cant have the $ thingies.

Please someone help? This is the last piece in the puzzle for a very important worksheet for our funding
Reply With Quote
  #9  
Old 11-13-2014, 08:01 PM
macropod's Avatar
macropod macropod is offline Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Windows 7 64bit Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,953
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

See attached.
Attached Files
File Type: xls Registration Sample.xls (95.5 KB, 9 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #10  
Old 11-14-2014, 12:25 AM
JaxV JaxV is offline Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Windows 7 32bit Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Office 2010 32bit
Novice
Counting unique visitors by ward, counting monthly visits by status, editing existing workbook
 
Join Date: Nov 2014
Posts: 8
JaxV is on a distinguished road
Default

Thank you that's perfect! Thank you thank you
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
counting colours not_dave Excel 1 12-29-2013 01:27 AM
Counting unique visitors by ward, counting monthly visits by status, editing existing workbook various data needs counting apples Excel 2 09-16-2012 04:52 AM
Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Counting Formula Karen615 Excel 6 06-20-2011 07:19 PM
Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Counting Colors g48dd Excel 2 03-13-2011 09:28 PM
Counting unique visitors by ward, counting monthly visits by status, editing existing workbook Counting Weeks leroytrolley Excel 1 08-18-2008 11:12 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:02 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft