Re: Vlookup across Multiple Sheets with multiple results
Thank you.
Re: Vlookup across Multiple Sheets with multiple results
Thank you.
Re: Vlookup across Multiple Sheets with multiple results
Do you want it to do just like 2-3 searches?
Re: Vlookup across Multiple Sheets with multiple results
Yeah, i'm thinking 2-3 words would be a perfect example.
Re: Vlookup across Multiple Sheets with multiple results
Well I did it, but it's as I feared. We are back to some noticeable lag.
Re: Vlookup across Multiple Sheets with multiple results
Hmm, lag is something I wanted to avoid. I guess that by adding that function, it's a tossup between convenience or lag.
Re: Vlookup across Multiple Sheets with multiple results
I'm assuming that without macros, the function of formulas is at its limit with that request, correct?
Re: Vlookup across Multiple Sheets with multiple results
Give me a few hours. I might have something genius
Re: Vlookup across Multiple Sheets with multiple results
LOL, something genius? That already describes what was created prior to this.
Re: Vlookup across Multiple Sheets with multiple results
Here is my example file. You can search up to 4 things and it's very hard to notice lag. (And sorry for taking so long, got caught up doing something last night)
Re: Vlookup across Multiple Sheets with multiple results
Oh wow! Thank you so much. Now, to modify this, is it the same as the earlier version? Just click and drag down with each formula for additional sheets?I did notice that partial search no longer works though.
Re: Vlookup across Multiple Sheets with multiple results
To put in more sheet, type the sheet name underneath the sheet header. To have more room for sheets, just drag the end of the formula (Cols B-E) down. The new formula automatically switches sheets at every purple box (located in Col F). The amount of rows you search for is changed underneath the Row Each header. (e.g. if Row Each is 30 it will do searches for all the sheets from rows 2-31, Row Each = 44 rows 2-45 searched). To fix the single search problem there is a few steps. Put this ---|=IFERROR(MID(RIGHT(Sheet4!$G$2,LEN(Sheet4!$G$2)-SUM(LEN(A$2:$A2))-COUNTA(A$2:$A2)),1,FIND(",",RIGHT(Sheet4!$G$2,LEN(Sheet4!$G$2)-SUM(LEN(A$2:$A2))-COUNTA(A$2:A2)),1)-1),IFERROR(RIGHT(Sheet4!$G$2,LEN(Sheet4!$G$2)-SUM(LEN(A$2:$A2))-COUNTA(A$2:$A2)),""))|--- into A3 and copy down 2 spaces. And underneath headers Search1-4 look at the end of the formula to find isblank($A$#)=FALSE and replace with len($A$#)>0. Then copy those formulas down.
Re: Vlookup across Multiple Sheets with multiple results
Thank you for the help!
Re: Vlookup across Multiple Sheets with multiple results
Did everything turn out the way you wanted and is working right?
Re: Vlookup across Multiple Sheets with multiple results
I tried pasting that formula into A3 on the Function tab and it kept giving me an 'Invalid Formula' error.**EDIT** I got the formula in A3 to work, but the edit to the formula in Search1 gives a #VALUE! error.
Re: Vlookup across Multiple Sheets with multiple results
Can you post both the formulas?
Re: Vlookup across Multiple Sheets with multiple results
This is in B2
=IF(AND(COUNTIF(INDIRECT(CONCATENATE("'",INDIRECT(ADDRESS(8+ROUNDDOWN((ROW()-2)/$A$21,0),1)),"'!",ADDRESS(ROW()-ROUNDDOWN((ROW()-2)/$A$21,0)*$A$21,1))),"=*" & $A$2 & "*")=1,LEN($A$2)>0),1,0)
Both A2 and B2 gives the #VALUE! error. I did nothing with A2.
Re: Vlookup across Multiple Sheets with multiple results
I made the changes to the formulas myself and they seem to be working. Here is the workbook
Re: Vlookup across Multiple Sheets with multiple results
I'm curious why my modification didn't work. Oh well..
Thank you for the amazing edit to this workbook. I believe I asked this before, but on the multiple search workbook, to add a new sheet is as simple as pasting the formulas down and adjust the 'Rows Each' cell, correct?
Re: Vlookup across Multiple Sheets with multiple results
Add in the new sheet underneath Sheets header. The formulas in columns B-D will change what sheet it's looking at after so many rows determined in Rows Each. So when 30 is in Rows Each it searches rows 2-31 for a match on all the sheets entered. (e.g. F2 is colored Purple cause that's where the first sheet's search begins. Look down Rows Each say 30 and you'll see another purple cell representing the start of the second sheet's search. Down another 30 rows another purple cell and so on and so on)
Re: Vlookup across Multiple Sheets with multiple results
Thank you so much for the help! It works perfectly and I managed to extend the ranges from 30 to 125 with little effort.
Now I need to work on somehow having the matching results highlight to the same color.
Don’t have an account yet? Register yourself now and be a part of our community!