Vlookup across Multiple Sheets with multiple results

    Re: Vlookup across Multiple Sheets with multiple results


    The function tab is incredibly easy to modify, but there are two things that I don't understand. I want to add another column to be searched and I also want to extend past the 'brown' area that designates the end of data.


    Any input would be greatly appreciated! Thank you.

    Re: Vlookup across Multiple Sheets with multiple results


    So instead of pulling out A,C,D columns you want a 4th? And by the 'brown' area do you mean the bar I put to show the end of the data of the Function sheet? If so, why would you want to extend past that?

    Re: Vlookup across Multiple Sheets with multiple results


    Yes, i'm trying to add column B into the search. In the function Tab, I added a 'All Col B' and adjusted the formula, but was unsure as to what else should be modified.


    As far as the brown bar goes, I have adjusted the search to 125 lines per sheet, so it has grown into a much larger project than I originally anticipated.

    Re: Vlookup across Multiple Sheets with multiple results


    Try this. Make a new column (All Col B) between "All Col A" and "All Col C". Then copy the formula from the first cell underneath "All Col A" into "All Col C". From there enter into the formula and change the last number from, what should be, 1 to 2. (1 = Column A, 2 = Column B, 3 = Column C and so forth)

    Re: Vlookup across Multiple Sheets with multiple results


    In sheet4 copy the formula to the correct column and make sure it points to "All Col B" in the Function sheet

    Re: Vlookup across Multiple Sheets with multiple results


    I can't beleive that I forgot about that formula on Sheet4! What about the brown line that signifies the end of data? Is there a way to modify that?

    Re: Vlookup across Multiple Sheets with multiple results


    There is, but I don't understand why you need data to go past that line? If you need more room to extract data copy the formula down or change the Rows Each number

    Re: Vlookup across Multiple Sheets with multiple results


    I must have been having a brain fart on that subject. There is a slight inquiry I have though. Currently, there are 15 sheets, 125 searches per sheet and they are requesting more fields to search. I am worried because processing time is starting to show. Is there another tweak that can be made to the formula or is this at its limit as far as processign time and searching?


    Thank you for the continued help!

    Re: Vlookup across Multiple Sheets with multiple results


    I was informed that 15 more tabs(sheets) will be added until everything is finished.


    A thought though. Instead of having roughly 30-35 sheets, what if I compiled them and increased the search from 125 to whatever I may need? Would that reduce processing time?

    Re: Vlookup across Multiple Sheets with multiple results


    If you reduced the sheets into one long lists of item/records I could make the process faster

    Re: Vlookup across Multiple Sheets with multiple results


    I tried modifying everything down to 4 sheets, with a search of 275 (one sheet had that many lines), but it reduced the processing speed tremendously. Now that I think of it, all of these sheets could be hidden, so I could work off one, single sheet and make other things for public viewing.


    Do you think it's possible to have no processing time if only a single sheet it used, regardless of it's size?

    Re: Vlookup across Multiple Sheets with multiple results


    If you put all the data onto one sheet you could create a table with it. From there who can filter/sort/organize your data in no time at all

    Re: Vlookup across Multiple Sheets with multiple results


    The sheets are only used for pulling data for the search function though. We don't really need the data for organization or charts.

    Re: Vlookup across Multiple Sheets with multiple results


    You could manually filter the data once it's in a table to retrieve only the things you want

    Re: Vlookup across Multiple Sheets with multiple results


    That would be a great option to use, but i'm working with others that are firm on a fast and reliable search function. If I compile everything to one sheet, then adjust the amount to be searched, do you think it would be continuoisly fast, no matter on the amounts of lines used?

    Re: Vlookup across Multiple Sheets with multiple results


    If you took all the time to put the records onto one sheet I don't understand why a click of a filter button is so hard?


    Anyways, if you put the records all onto one sheet I could modify the code to be significantly faster. And as with any program, the larger the data set the slower it takes to compute. What will be the trend of processing time as the data gets larger in a linear fashion? Well, I don't know until I test it, but I do know it will get slower :)

    Re: Vlookup across Multiple Sheets with multiple results


    Yeah, I know that would work best, but when certain people get thoughts in their heads, it's hard to deter them away.


    I placed every sheet into one, then set the search line to 800. Testing it multiple times, with up to 4 searches at once, it doesn't take longer then 1 second to load the results. I am curious towards how long that will last, with a possible 800 more lines being added soon. How would a modified formula work towards a larger sheet like this?


    My other thought was several more sheets, all with around 50 lines for the search. That could possibly be faster?

    Re: Vlookup across Multiple Sheets with multiple results


    Quote


    Yeah, I know that would work best, but when certain people get thoughts in their heads, it's hard to deter them away.


    I literally just laughed. I can relate to that, and it can be very frustrating.


    If we are going to go with the one sheet approach, I could take out all the indirects and addresses and this and that and make it all point to one sheet. Making the processing time seamless.


    Hmmmmm. My answer to that is no. LOL. The more sheets the more I referencing and calculations have to happen slowing down processing time, and increasing my headache :)

    Re: Vlookup across Multiple Sheets with multiple results


    It is extremely frustrating! haha


    Yes, i'm thinking the one sheet approach would work the best. I can modify the one sheet to be edited in the future.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login