User Profile
Mark_Bry
Copper Contributor
Joined Apr 24, 2020
User Widgets
Recent Discussions
Re: Combine VLOOKUP and Data Validation drop list
Hi Peter Thanks for coming back to me on this. Unfortunately I had to strip out most of the info from the sheet to share online so it's not a great example. Basically on the front sheet when you select the client it adds the contact and address etc. The trouble I have is one of our clients have about 5 contacts. I have been using VLOOKUP which works fine for all the sites with only 1 contact. Bristol121 however will only show the top result in the lookup. I want to know if only when more than one result they can appear in a drop down list in the contacts cell? I added a =IF(B11="Bristol 121",'Data Sheet'!$B$8:$B$15) in the data validation but it overwrites the VLOOKUP formula rather than work off the back of it. (I may be using the wrong formula but my knowledge is basic. peteryac6024KViews0likes1CommentCombine VLOOKUP and Data Validation drop list
I am trying to use VLOOKUP which works but I want to know if there are multiple results in the table_array can it combine with data validation. I can get them both working but not together. In data validation source i have put =IF(B11="Bristol 121",'Data Sheet'!$B$8:$B$15) For the Vlookup I have put =VLOOKUP(B11,Table10[#All],2,FALSE) Basically if Bristol 121 is selected then it will add a drop list for the contacts for that site. on the front page. I have attached a test sheet for an example24KViews0likes5CommentsRe: If column 1 in the top table = 'yes' I want to copy column 2 & 3 into the table below columns 1 & 2.
wumolad Hi There. Quick question. I have been trying to understand the formula you sent previously. I get the first part being the array etc. I still cant get it to work on another table? I have added the following to a table attached: =IFERROR(INDEX(Table13[[#All],[Tool List]], SMALL(IF(Sheet2!C44=Table13[[#All],[Require/Omit]],Table13[[#All],[Require/Omit]])-ROW($A$43), ROW(1:1))),"" ) I sure it the red bit that I have wrong but the more I try the more confused I get. Can you advise where/why I have gone wrong please?7.4KViews0likes1CommentRe: If column 1 in the top table = 'yes' I want to copy column 2 & 3 into the table below columns 1 & 2.
mtarler Yes - While at home I thought I would put together a set of RAMS which can be used/changed for all sites by just selecting info. As you will know it takes a while to do normally. The RA is similar to one I seen online which I liked, so changed the layout/content slightly to suit what I'm trying to achieve. Just trying to get the data input part sorted then I can add a lot of info plus the standard bits. To be honest with my basic knowledge of Excel I have been amazed how little I know and how much you can actually do. Thanks to both of you for your help in getting me to this point. I'm hoping I will be able to do the rest myself (the easy part). I really would not of been able to work it out without your guidance and support. Very much appreciated!7.5KViews0likes0CommentsRe: If column 1 in the top table = 'yes' I want to copy column 2 & 3 into the table below columns 1 & 2.
Thanks again! I thought that part was to remove the blank rows - I get it now! Although I do have 2 more questions (Sorry). I have attached the updated version again. Is there any way to keep the row height auto fill once you un select from the info sheet? When I take out a row of info I want it to go back to a standard height automatically. Is this possible? Also Im trying to populate most of the info on the whole workbook by checking the info on the site row (Info sheet) and then select the site on page 1. It is all working as I want apart from the site contacts on page 3. If no entry then it shows 0. Ive added an IFERROR but cant get rid of it? If you would be so kind to try and answer the questions it will be really appreciated! I cant tell you how much I have learned and benefited from your answers/solutions. wumolad7.5KViews1like3CommentsRe: If column 1 in the top table = 'yes' I want to copy column 2 & 3 into the table below columns 1 & 2.
wumolad Me again -Sorry! It worked perfectly with 4 column. Now that I have made the sheet to look more like how its going to end up I can't get the formulas to work? In fact some info have been put into the Cells when they shouldn't. I have been trying for the last 3 hours to work out what I have done wrong but must be missing something stupid? Can you have a quick look at the attached and let me know how I have messed it up if possible. Page 4 is sheet in question and the data is on the 'Risk Info' sheet.7.5KViews0likes7CommentsRe: If column 1 in the top table = 'yes' I want to copy column 2 & 3 into the table below columns 1 & 2.
wumolad Thanks for the helping on this one, although it is still not working. I get a 0 entered in column 1 of the second table. I'm not great on excel yet so your formula went straight over me head (got it sort of). I may not of explained very well. I want a hidden data sheet with lots of rows showing info. When a row is selected by picking yes from the drop down in column 1 then columns 2 & 3 in that row is added to a table on another sheet which is not hidden. We can then pick and chose which bits of info goes in the table which can be viewed to send out. By selecting yes automatically adds the info. The tables I attached was so that I could test in principal then alter the cells/sheet in the formula to suit. I really do appreciate any help on this.7.7KViews0likes11CommentsIf column 1 in the top table = 'yes' I want to copy column 2 & 3 into the table below columns 1 & 2.
I have basic knowledge in Excel. I want to know how to do the following: If cells in column 1 in a table = 'yes' I want to copy that row in column 2 & 3 into a separate table. I have been trying different formulas but its above me. Please can you help by giving the correct formula. Or at least point me in the right direction.Solved8.2KViews0likes14Comments
Recent Blog Articles
No content to show