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

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

Registrations Sample.xls (85.5 KB, 15 views)

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

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

Got it Still need help with the first issue but think I have the second sorted now

What is the first issue please?

__________________
Using O365 v2407 - 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

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

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

See attached.

Attached Files

Registration Sample.xls (95.5 KB, 11 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you that's perfect! Thank you thank you