Using this code to fill in data from a address book
=IF(ISNA(VLOOKUP($C$13,'Address Book'!A1:C189,2,FALSE)),"",VLOOKUP($C$13,'Address Book'!A1:C189,2,FALSE))
Is there a way around that I can input the data without it being in address book? So if its in address book it will auto fill,but if not in address book I can still input the data?
Thanks
Last edited by zplugger; 09-07-2017 at 02:03 PM.
The obvious answer is to overwrite the formula - but this would not update later if address book updated.
How about some VBA to allow you to update the address book whenever the lookup finds nothing?
I am puzzled by your formula...
Formula:
Please Login or Register to view this content.
$C$13 does not change as you copy down. Is your lookup value always in the same cell C13?
A1:C189 changes to A2:C190 etc as you copy down - I would not expect the lookup table itself to change
Last edited by kev_; 09-04-2017 at 03:02 AM.
Click *Add Reputation to thank those who helped you. Ask if anything is not clear
Thanks, maybe this will explain better.
I have amended 2 things - see attached file for working example
1. Data validation on C13 Error Alert set to "Warning" (was "Stop")
- allows user to enter any value into cell C13
2. IF statement wrapped around your Change event code
- makes it apply only to cell C13
- which prevents VBA overwriting manual entries in C14 & C15 with blanks
Thank You kev_ work like a charm
You are welcome
Thanks for the reps
Ran into a problem,I have merge cells in C14,C15. Is there a way around this? Works perfect if there is no merge cells,but sad to say I have some.
Merged Cells and VBA do not mix very well. I will look at your file later today
C14 and C15 Merged
see attached workbook
Please Login or Register to view this content.
Hello kev_
Could not make the code work?, I must be doing something wrong.
I think I miss led you kev_
Cells C13,D13,E13,F13 are merge in my workbook
Cells C14,D14,E14,F14 are merge in my workbook
Cells C15,D15,E15,F15 are merge in my workbook
Not sure there is a way around this,works OK if address is in address book,its the manual input killing me.
Last edited by zplugger; 09-06-2017 at 07:05 PM.
Hello zplugger,
Hope you are well!
Please try this Code:
Regards.Please Login or Register to view this content.
Please consider:
Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
Thanks Windon, seems to work just find. Not sure why G24 has Data Validation, was not in my example book?
Hello again Windon
Is there a way we can not have the warning box and select from dropbox msg. Click the cell and drop down shows normal,and if you don't select from drop down you can manually time in name and address. Just trying to make it simple?
Hi zplugger,
I don't have a Wind on yet. In the meantime, you may call me Winon,LOL
Replace my Code in the sample Workbook with the Code below;
Kind Regards.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks