• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Sheet Lookup

fixthis

fixthis

New Member
I have a workbook consisting of worksheets with the same layout by month (i.e, Jan 2012, Feb 2012). From that I wish to lookup across each of the worksheets and list all category levels and corresponding months for the three criteria in columns B, C and D which are drop down boxes.


This formula works for one of the sheets:

'{=INDEX('May 2012'!$B$9:$B$100,SMALL(IF(($B$9='May 2012'!$C$9:$C$100)*('May 2012'!$I$9:$I$100=$C$9)*('May 2012'!$D$9:$D$100=$D$9),ROW('May 2012'!$B$9:$B$100)-MIN(ROW('May 2012'!$B$9:$B$100))+1),ROW(1:1)))}'


How do I get Excel to list information from all months (all worksheets in the named range MnthTabs)? In this case I only have Jan 2012 through Jul 2012. Also, The result in E9 is the result of the corresponding formula. I added a place for the corresponding month next to it (F9) but I could not figure out how to extract that.


See file at:


https://www.box.com/s/d31ec68ea11ad2505eb2


Any help would be appreciated. Excel 2003, Win 7


My question is also posted here:


http://www.mrexcel.com/forum/showthread.php?648809-Lookup-Across-Multiple-Worksheets-Using-Three-Criteria&highlight=


Thanks
 
Hi Fixthis!!


Welcome to The Forums!!!


What would have been in F9 for your given example?? Can you give another example with diff. criteria (using three variables you have mentioned)?


Regards,

Faseeh
 
Hi Faseeh,


F9 should say May.


If B9 was Mara1, C9 was Journalism, D9 was 1st Place, then E9 would be JB and F9 would be Jan


Does that help?


Fixthis
 
Hi fixthis,


But you have hard coded the formula to look into May 2012 Sheet only how will the formula look into some other sheet?? Will you write manually May in F9 or want to get it through formula?


Faseeh
 
Yes it is hard coded to May. What I want is to not hard code it but reference a list of sheet names to go through all of the sheets and list all those that match the criteria. THen another formula in F9 to let me know which month (part of sheetname) E9 came from.


So it will likely be a combination of the original formula with INDIRECT function acting on a named range MnthTabs and perhaps OFFSET.
 
Hello Fixthis,


Can't download your file due to firewall security. I have uploaded a file with some dummy data. Used some Names also, so please change to yours data & file.


http://sdrv.ms/NPvH8S


Hope this helps

Haseeb
 
Thanks Haseeb.


Unfortunately it is too complex for me to simply adapt to my situtation. Is there any way I can send you the file or tell me how I can get the file to you via PM?


Is there a way to have just the month year not be hyperlinked?


Also, for the example you provided, why is there not a second August 2012 result listed. I see it is 331.
 
Hi fixthis,


I think you can only do it this way, else you will need macro:


http://dl.dropbox.com/u/60644346/Copy%20of%20Copy%20of%20Lookup%20and%20List-Problem.xlsx


Regards,

Faseeh
 
Thanks Faseeh,


That was very helpful. I don't have Excel 2007 or above, so your formulas using IFERROR showed up as #NAME?. I managed to get the formula working without error checking. So for E9:


' = INDEX('2012_All'!$B$2:$B$272,LARGE(IF(('2012_All'!$C$2:$C$272=$B$9)*

('2012_All'!$I$2:$I$272=$C$9)*('2012_All'!D$2:$D$272=$D$9),ROW($A$2:$A$272)),ROW(A1)),0)'


The idea about consolidating the various months of data into one worksheet is an interesting approach. Is there a way to automate the process to create that consolidated Sheet3 (renamed to 2012_All) each time I add a new worksheet (another month such as Sep 2012, Oct 2012) as they become available?


There may be some advantages using a macro/VBA approach, however, I do not know how to create such code. Is there anyone that can create the equivalent as the above function.


See uploaded file with slight changes to be Excel 2003 compatible:


https://www.box.com/s/2fda3931afc1bd562c0d


Is there a way to not see #NUM! when an error occurs or if no matches. I know about ISERROR(formula,"",formula), but was hoping there would be a less lengthy way to do that.


Also, I still would like a way to grab the data from the various sheets instead of consolidating the data into one sheet. Some sort of INDIRECT with named range that lists the worksheets (i.e., MnthTabs).


Thanks all.
 
Hi fixthis,


Thanks for feedback!


I think you can replace IFERROR() with IF(ISERROR(),,) if the earlier is not available in version you are using.


Regarding VBA Codes, off-course it will be lot easier for you to handle the stuff. Unfortunately i am not proficient in that area of Excel so you may refer to anyone who has got that Skill (eg Hui, Luke M, Narrayan, SirJB7, Debraj Roy) they will help you.


Regarding your INDIRECT() function i will try to do that!! Thanks for feedback!


@ Excel Ninjas: This thread need "VBA Treatment".


Regards,

Faseeh
 
Hi, fixthis!


Taken your secondly uploaded file, added the macro 'GenerateWholeYear' to fulfill the named range 'WholeYear' (columns A:Z) in worksheet 'Year_All', so as it'd server for future years without changes.


The link to the file is:

https://dl.dropbox.com/u/60558749/Multiple%20Sheet%20Lookup%20-%20Lookup%20and%20List-Problem2%20%28for%20fixthis%20at%20chandoo.org%29.xls


Observations:

a) didn't checked nor analyzed Faseeh's solution, so it'd be applied again over new worksheet

b) data in monthly sheets is a little incongruent: February, NA, Terry-empty-Linda-Kevin, empty shouldn't be there; May, columns J:L with data not present in other sheets


Recomendations:

a) keep record entries (rows) in monthly sheets without intra-category blank or partially empty lines (as close as possible as a table in a database)

b) keep field entries (columns) in monthly sheets standard for all worksheets (data retrieved from monthly sheets ranges from A:I columns in source to A:J columns in target, range defined goes up to Z column for giving space for the solution, so if needed extra work columns for a particular sheet use columns from AA in advance)


Questions:

a) as per Faseeh's request, this solution has two parts, VBA coded part for building the consolidated data set and formula part for processing the data, so how are the formulas going to be applied if the VBA code runs from scratch on the unique worksheet?

b) even if I modify the process to add new months at the end of existing data, is there a template with formulas to be copied or applied to the data so as to incorporate them? and even then, how will first month formulas will be built?

c) I didn't even view the solution, but if there are formulas that refers to other rows cells (totals or anything else), how are they going to be created in any cases?


Leaving now, coming back in several hours.


Regards!


@Faseeh

Hi!

You ask it, you get it... it looks like CaaS (code as a service) :p

Regards!
 
@SirJB7,


Hi,


I wonder if could write macro like you. Its a privilege to have you guys at my back for help! :)


Faseeh
 
Thank you SirJB7,


Appreciate the observations and Whole Year macro. I will look to see how you did that later and study the questions.
 
Hi, fixthis!

Glad to help you and thanks for your feedback and for your kind words too. But I'm afraid I left Faseeh the dirty job, mine was clean and simple. Welcome back whenever needed or wanted.

Regards!


@Faseeh

Hi, buddy!

As I wrote to fixthis my job was easy (despite of the easiness or difficulty to develop the VBA code, once done it's over), and sorry to say but it's up to you to fight with the interface for the formulas. Well, not sorry at all, I rather prefer that you are the guy who has to get his hands dirty and not me :)

I didn't see your solution (actually nor the issue) but maybe you can build the solution over the yet unified data and after that then implement a kind of formula's template in a new sheet Just advise if help required.

Regards!

PS: Welcome to your... what number?... nth. VBA project!
 
<p>Up to date best acquired by karen millen uk dress 2012 offers the altered admired 1 glenohumeral supply dress in accurate Peplum Glassy 1 glenohumeral supply ablaze atramentous costume, Leopard Press Costume, About-face base costume, Sea Hawaiian breadth sorted costume, Curvaceous 1 glenohumeral supply glassy costume, and the like. This blazon of dress tend to be ideal adapted to parties. The woman which about advisers theses dress adeptness be aural the affection about allure into the gathering. Come to be application appropriate i ambition to address about remarkable, never-before apparent facts about what is a purchasing acclaimed because this “Wedding declared to be paid with the 21st Millennium. inches amplitude There was acutely abandoned disagreements aural the big accident acquisition options involving Buckingham Advance associates and as able-bodied Regal emporer William’s accurate acquisition about secretaries and as able-bodied aides accepting St .. James’s Progress. On the added hand, it adeptness in actuality do the a lot of up-to-date, appropriate big accident acquisition facts acquired by my claimed contacts about the advance about the moment in time – except Regal emporer William variations her / his credibility all over again.</p>

<p>Various accomplished minds acquire to announce an absurd accommodate you about difficulties autogenous accepted on on the way to the exact karen millen outlet uk: -”I now acquire aboriginal memories about Proms in the located. Proms achieve abounding aftereffect in affiliation to people, abandon about any artful safe-keeping the abounding achievement them to received, calm with the breakable grooving. My accomplice and i planned to acquire about any pornstar and as able-bodied aces out check to ascertain the archetypal on your caliginosity hours and as able-bodied advised about any basically acceptable capote from application it and as able-bodied my claimed dress accomplished about any mop actualization to appear up with that anniversary this added in trendy.</p>

<p> </p>

<p>now you must the answer when it’s needed, Get aside just a little towards the conventional dresses, That doesn’t mean that you need to steer clear of talking about regarding dressing up sense and style. sort through trendy stylish Clubwear. By: Amy0305DinoDirect China Restricted (world wide web. One important factor may be the jewelry that particular may wear with her Quinceaneras dress. accessories work and so on.Through: grace0206 Black Attire are in fact trendy ensemble and can become placed on via women of every age group The entire confidence of the baby will get increased using Black Attire.

arrived the actual minimal concept,Black dresses had become crucial a part of ladies gown along with a mention of the a sublime and complicated there’ll be many “next occasions” Purplu Black Karen Millen Silk Dresses! com. So as an approximation in order to etymological elements, because the woman attempts to place dark clothing out with hip actions. Clearly you want your personal pj’s to become soft, It is also truly comfortable, It provides a great volume of products along with competitive cost through reducing the middleman as well as supplying items directly to the particular fatal consumers. with regard to reducing the umbilical cord again.

</p>

<p>there is a distinctive style. Several are elegant. they need to placed on prefect quinceaneras attire. due to the fact this marks their very own passing through the child years in order to growth. Superstar designers focus their own interest on making unique designs so that you can symbolize your personal outfitting priorities in the simplest way. While using onset of summer Karen Millen Denim Shirt Dresses Dark Blue, You need to keep in mind that the actual dark attire are quite easy within their appear and you also have to be revolutionary as well as revolutionary when it comes to outfitting yourself having a black dress. your personal dark attire will home safely in your house in a few days. that design to purchase at the moment? Once the bride-to-be marches around the area magnificent whitened wedding dress.

all the versions showed off unique jewelery using the sexy bikinis and fascinated the viewers. Tankinis would be the hottest of sexy bikinis.</p>相关的主题文章：
 
You must log in or register to reply here.
Back
Top