Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Restrictions on Addresses
Hey
We currently use a spreadsheet on office 2003 to store a large amount of personal information regarding property work. This spreadsheet is populated by opertives who answer phone calls and enter the information onto it, Recently we have found some sites that are no longer covered by us. I was thinking if we could put a restriction on the spreadsheet so when an operative tries to enter an address that is restricted then it will bring up a warning to say that its restricted and a reason why. Can this be done? Hope to hear from you soon. James (Technology Officer) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Restrictions on Addresses
Your going to need a list of the restricted addresses or some identifiable
feature that they all have in common. "Bunnyboy" wrote: Hey We currently use a spreadsheet on office 2003 to store a large amount of personal information regarding property work. This spreadsheet is populated by opertives who answer phone calls and enter the information onto it, Recently we have found some sites that are no longer covered by us. I was thinking if we could put a restriction on the spreadsheet so when an operative tries to enter an address that is restricted then it will bring up a warning to say that its restricted and a reason why. Can this be done? Hope to hear from you soon. James (Technology Officer) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Restrictions on Addresses
The thing is there are no definate sites for us. We can go to any property
within 20 miles. I basically wanted a seperate sheet we could add these addresses with the name and a reason why and a warning if any of those addresses were entered on the main sheet. "Lee Diggins" wrote: Hi James Would you want the record added even if you don't cover that site? If not, you could do this using Data Validation using the List option, pointing to a named range stored in a hidden worksheet containing the allowed sites. Maybe have a read about Data Validation and post back if you need any further clarification or if you don't feel it does not acheiev what you're after. Regards, Lee "Bunnyboy" wrote: Hey We currently use a spreadsheet on office 2003 to store a large amount of personal information regarding property work. This spreadsheet is populated by opertives who answer phone calls and enter the information onto it, Recently we have found some sites that are no longer covered by us. I was thinking if we could put a restriction on the spreadsheet so when an operative tries to enter an address that is restricted then it will bring up a warning to say that its restricted and a reason why. Can this be done? Hope to hear from you soon. James (Technology Officer) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Restrictions on Addresses
Sorry am getting confused the addresses you are refferring to are house
addresses not web addresses? and you are wanting to allert the end user if the address they enter is beyond 20miles of a base address? If this is correct then you could use a sub to calculate distance to address by post code / zip code! Alternatively if you are creating a list of addresses you could check for these using "find" when data is entered! Is data entered by user form or directly into the cells of a worksheet? "Bunnyboy" wrote: The thing is there are no definate sites for us. We can go to any property within 20 miles. I basically wanted a seperate sheet we could add these addresses with the name and a reason why and a warning if any of those addresses were entered on the main sheet. "Lee Diggins" wrote: Hi James Would you want the record added even if you don't cover that site? If not, you could do this using Data Validation using the List option, pointing to a named range stored in a hidden worksheet containing the allowed sites. Maybe have a read about Data Validation and post back if you need any further clarification or if you don't feel it does not acheiev what you're after. Regards, Lee "Bunnyboy" wrote: Hey We currently use a spreadsheet on office 2003 to store a large amount of personal information regarding property work. This spreadsheet is populated by opertives who answer phone calls and enter the information onto it, Recently we have found some sites that are no longer covered by us. I was thinking if we could put a restriction on the spreadsheet so when an operative tries to enter an address that is restricted then it will bring up a warning to say that its restricted and a reason why. Can this be done? Hope to hear from you soon. James (Technology Officer) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Restrictions on Addresses
Hi James
Would you want the record added even if you don't cover that site? If not, you could do this using Data Validation using the List option, pointing to a named range stored in a hidden worksheet containing the allowed sites. Maybe have a read about Data Validation and post back if you need any further clarification or if you don't feel it does not acheiev what you're after. Regards, Lee "Bunnyboy" wrote: Hey We currently use a spreadsheet on office 2003 to store a large amount of personal information regarding property work. This spreadsheet is populated by opertives who answer phone calls and enter the information onto it, Recently we have found some sites that are no longer covered by us. I was thinking if we could put a restriction on the spreadsheet so when an operative tries to enter an address that is restricted then it will bring up a warning to say that its restricted and a reason why. Can this be done? Hope to hear from you soon. James (Technology Officer) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Restrictions on Addresses
ok lol
Let me start again. We use a spreadsheet to collect information from customers who require us to attend to a property to do repairs. Occasionally we may come across a property that our workers won't want to go back too for a specific reason such as the property was messy or the customer was abusive. What i wanted was somewhere we could put these addresses and the reason why we won't go back. Then if the customer rings again and someone tries to put the address in then a warning or an error will come up saying that the property is blacklisted and why. Is this possible? James "Atishoo" wrote: Sorry am getting confused the addresses you are refferring to are house addresses not web addresses? and you are wanting to allert the end user if the address they enter is beyond 20miles of a base address? If this is correct then you could use a sub to calculate distance to address by post code / zip code! Alternatively if you are creating a list of addresses you could check for these using "find" when data is entered! Is data entered by user form or directly into the cells of a worksheet? "Bunnyboy" wrote: The thing is there are no definate sites for us. We can go to any property within 20 miles. I basically wanted a seperate sheet we could add these addresses with the name and a reason why and a warning if any of those addresses were entered on the main sheet. "Lee Diggins" wrote: Hi James Would you want the record added even if you don't cover that site? If not, you could do this using Data Validation using the List option, pointing to a named range stored in a hidden worksheet containing the allowed sites. Maybe have a read about Data Validation and post back if you need any further clarification or if you don't feel it does not acheiev what you're after. Regards, Lee "Bunnyboy" wrote: Hey We currently use a spreadsheet on office 2003 to store a large amount of personal information regarding property work. This spreadsheet is populated by opertives who answer phone calls and enter the information onto it, Recently we have found some sites that are no longer covered by us. I was thinking if we could put a restriction on the spreadsheet so when an operative tries to enter an address that is restricted then it will bring up a warning to say that its restricted and a reason why. Can this be done? Hope to hear from you soon. James (Technology Officer) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Restrictions on Addresses
Absolutely it is possible! Do you have an idea of what a new worksheet would
be called and which range of cells would contain which aspects of the address and where the reason would be entered? Do you know how data is entered? A user form would sound ideal for this sort of task! There may be an issue to consider of what if an address is entered slightly different to that on the black list, if searching for an exact match then it may not show up! Is there any other identifier for each customer such as a customer refference number or post code etc "Bunnyboy" wrote: ok lol Let me start again. We use a spreadsheet to collect information from customers who require us to attend to a property to do repairs. Occasionally we may come across a property that our workers won't want to go back too for a specific reason such as the property was messy or the customer was abusive. What i wanted was somewhere we could put these addresses and the reason why we won't go back. Then if the customer rings again and someone tries to put the address in then a warning or an error will come up saying that the property is blacklisted and why. Is this possible? James "Atishoo" wrote: Sorry am getting confused the addresses you are refferring to are house addresses not web addresses? and you are wanting to allert the end user if the address they enter is beyond 20miles of a base address? If this is correct then you could use a sub to calculate distance to address by post code / zip code! Alternatively if you are creating a list of addresses you could check for these using "find" when data is entered! Is data entered by user form or directly into the cells of a worksheet? "Bunnyboy" wrote: The thing is there are no definate sites for us. We can go to any property within 20 miles. I basically wanted a seperate sheet we could add these addresses with the name and a reason why and a warning if any of those addresses were entered on the main sheet. "Lee Diggins" wrote: Hi James Would you want the record added even if you don't cover that site? If not, you could do this using Data Validation using the List option, pointing to a named range stored in a hidden worksheet containing the allowed sites. Maybe have a read about Data Validation and post back if you need any further clarification or if you don't feel it does not acheiev what you're after. Regards, Lee "Bunnyboy" wrote: Hey We currently use a spreadsheet on office 2003 to store a large amount of personal information regarding property work. This spreadsheet is populated by opertives who answer phone calls and enter the information onto it, Recently we have found some sites that are no longer covered by us. I was thinking if we could put a restriction on the spreadsheet so when an operative tries to enter an address that is restricted then it will bring up a warning to say that its restricted and a reason why. Can this be done? Hope to hear from you soon. James (Technology Officer) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Restrictions on Addresses
It would just be a work sheet called blacklist. I'm not sure of the range of
cells yet as i was just thinking about it. We only enter the information into the cells as we arrnage appointments based around areas so it has to be cell entry. I can get the addresses entered in a uniform way so that they're always entered the same way. Post codes wouldn't work as sometimes an entire street will have the same postcode. If you guys could tell me how to do it, I can test it and see how it works. "Atishoo" wrote: Absolutely it is possible! Do you have an idea of what a new worksheet would be called and which range of cells would contain which aspects of the address and where the reason would be entered? Do you know how data is entered? A user form would sound ideal for this sort of task! There may be an issue to consider of what if an address is entered slightly different to that on the black list, if searching for an exact match then it may not show up! Is there any other identifier for each customer such as a customer refference number or post code etc "Bunnyboy" wrote: ok lol Let me start again. We use a spreadsheet to collect information from customers who require us to attend to a property to do repairs. Occasionally we may come across a property that our workers won't want to go back too for a specific reason such as the property was messy or the customer was abusive. What i wanted was somewhere we could put these addresses and the reason why we won't go back. Then if the customer rings again and someone tries to put the address in then a warning or an error will come up saying that the property is blacklisted and why. Is this possible? James "Atishoo" wrote: Sorry am getting confused the addresses you are refferring to are house addresses not web addresses? and you are wanting to allert the end user if the address they enter is beyond 20miles of a base address? If this is correct then you could use a sub to calculate distance to address by post code / zip code! Alternatively if you are creating a list of addresses you could check for these using "find" when data is entered! Is data entered by user form or directly into the cells of a worksheet? "Bunnyboy" wrote: The thing is there are no definate sites for us. We can go to any property within 20 miles. I basically wanted a seperate sheet we could add these addresses with the name and a reason why and a warning if any of those addresses were entered on the main sheet. "Lee Diggins" wrote: Hi James Would you want the record added even if you don't cover that site? If not, you could do this using Data Validation using the List option, pointing to a named range stored in a hidden worksheet containing the allowed sites. Maybe have a read about Data Validation and post back if you need any further clarification or if you don't feel it does not acheiev what you're after. Regards, Lee "Bunnyboy" wrote: Hey We currently use a spreadsheet on office 2003 to store a large amount of personal information regarding property work. This spreadsheet is populated by opertives who answer phone calls and enter the information onto it, Recently we have found some sites that are no longer covered by us. I was thinking if we could put a restriction on the spreadsheet so when an operative tries to enter an address that is restricted then it will bring up a warning to say that its restricted and a reason why. Can this be done? Hope to hear from you soon. James (Technology Officer) |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Restrictions on Addresses
OK one way would be to put a command button ((view-control tollbox-design
mode)then drag command button onto main sheet) onto your main sheet, label it something like "check Address" then insert the following sub Private Sub CommandButton1_Click() With Worksheets("blacklist").Range("A:A") If ActiveCell.Value < "" Then Set c = .Find(ActiveCell.Value, LookIn:=xlValues) If Not c Is Nothing Then Dim Res As Long Res = MsgBox(c.Offset(0, 1).Value) Cancel = Res < 6 End If End If End With End Sub If the end user selects a cell containing an address then clicks this command button it will throw up a message box that would contain the information in the cell next to the address on the blacklist sheet. So if you have addresses in column a of the blacklist worksheet and reasons for blacklisting the address in column B it will display the reason for blacklisting in the message box when the address of the selected cell is identical to one in column a blacklist. "Bunnyboy" wrote: It would just be a work sheet called blacklist. I'm not sure of the range of cells yet as i was just thinking about it. We only enter the information into the cells as we arrnage appointments based around areas so it has to be cell entry. I can get the addresses entered in a uniform way so that they're always entered the same way. Post codes wouldn't work as sometimes an entire street will have the same postcode. If you guys could tell me how to do it, I can test it and see how it works. "Atishoo" wrote: Absolutely it is possible! Do you have an idea of what a new worksheet would be called and which range of cells would contain which aspects of the address and where the reason would be entered? Do you know how data is entered? A user form would sound ideal for this sort of task! There may be an issue to consider of what if an address is entered slightly different to that on the black list, if searching for an exact match then it may not show up! Is there any other identifier for each customer such as a customer refference number or post code etc "Bunnyboy" wrote: ok lol Let me start again. We use a spreadsheet to collect information from customers who require us to attend to a property to do repairs. Occasionally we may come across a property that our workers won't want to go back too for a specific reason such as the property was messy or the customer was abusive. What i wanted was somewhere we could put these addresses and the reason why we won't go back. Then if the customer rings again and someone tries to put the address in then a warning or an error will come up saying that the property is blacklisted and why. Is this possible? James "Atishoo" wrote: Sorry am getting confused the addresses you are refferring to are house addresses not web addresses? and you are wanting to allert the end user if the address they enter is beyond 20miles of a base address? If this is correct then you could use a sub to calculate distance to address by post code / zip code! Alternatively if you are creating a list of addresses you could check for these using "find" when data is entered! Is data entered by user form or directly into the cells of a worksheet? "Bunnyboy" wrote: The thing is there are no definate sites for us. We can go to any property within 20 miles. I basically wanted a seperate sheet we could add these addresses with the name and a reason why and a warning if any of those addresses were entered on the main sheet. "Lee Diggins" wrote: Hi James Would you want the record added even if you don't cover that site? If not, you could do this using Data Validation using the List option, pointing to a named range stored in a hidden worksheet containing the allowed sites. Maybe have a read about Data Validation and post back if you need any further clarification or if you don't feel it does not acheiev what you're after. Regards, Lee "Bunnyboy" wrote: Hey We currently use a spreadsheet on office 2003 to store a large amount of personal information regarding property work. This spreadsheet is populated by opertives who answer phone calls and enter the information onto it, Recently we have found some sites that are no longer covered by us. I was thinking if we could put a restriction on the spreadsheet so when an operative tries to enter an address that is restricted then it will bring up a warning to say that its restricted and a reason why. Can this be done? Hope to hear from you soon. James (Technology Officer) |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Restrictions on Addresses
Private Sub CommandButton1_Click()
With Worksheets("blacklist").Range("A:A") If ActiveCell.Value < "" Then Set c = .Find(ActiveCell.Value, LookIn:=xlValues) If Not c Is Nothing Then Dim Res As Long Res = MsgBox("Address is Blacklisted:" & vbCrLf & c.Offset(0, 1).Value) Cancel = Res < 6 End If End If End With End Sub This variant adds the line "Address is Blacklisted" before the reason why, or could contain any other comment you wished to place between the "" in the sub. When Addresses are input is the entire address entered into one cell or is it split across cells? ie street name and number in collumn A , town in column B and so on? and would you be looking to use the same format on the blacklist? If so the sub above would need to be changed to compare all cells containing elements of the address. All the best John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RESTRICTIONS | Excel Discussion (Misc queries) | |||
How to use SMALL with certain restrictions? | Excel Discussion (Misc queries) | |||
How do I set up restrictions in my documents? | New Users to Excel | |||
Printing with restrictions - VB | Excel Discussion (Misc queries) | |||
How to do look up with restrictions | Excel Discussion (Misc queries) |