Please reference the attached file.
In column C, I need a formula that locates the correct data from the Historical tab. It first needs to match Company ID, Year, Period, and then the correct categories (ie RR Sales, MS Costs, and so on).
Do I use a SUMPRODUCT*OFFSET with multiple Match inserts? If so, how do I put that together?
*Prior year should just be Current Year minus 1 (B1(2014)-1=2013). I'm sure you could have figured that out though.
Thanks!
Try this little trick to simplify things.
1st, in Historical B4, copy this across (you can hide it if you want)...
=B1&B2&B3
Then in Prior, use this, copied down...
=IF(A8="","",INDEX(Historical!$A:$E,MATCH('Prior YTD'!$A8,Historical!$A:$A,0),MATCH($B$4&$B$1&$B$2,Historical!$A$4:$E$4,0)))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I'm receiving the #N/A message. I did part 1 to create a unique company id (good helpful trick btw) but when I paste the formula it continues to give the error. Any thoughts?
Any way you can upload a file with the formula working?
AFTER FURTHER REVIEW...
I got to formual to work but only with certain values in B1, B2, and B4. For instance, B1-2012; B2-1; B4-Company B works but B1-2013; B2-2; B4-Company B does not.
I attached an updated file with formula not always working
Thanks again
COMPANY A20121 COMPANY A20132 COMPANY B20121 COMPANY B20132 theer are no matches for
20122 or 20131
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Sorry, I forgot to add that, given your data and the criteria, there were no matches for the Company, year and month you selected, I had to pick my own to test with.
Thanks for the assist, martin
Aaahh. I confused myself with the year and period labelings trying to condense everything into mini sample to upload.
This forum is awesome but I am interested in understanding how someone knowledgable approaches an excel formula problem. I want to know what you look at first, what questions you ask yourself to start, what steps do you take, how do you piece things together. Stuff like that so I'm not always coming here asking "How do I do this whole formula?". Is there an area dedicated on this site to formula building methods/techniques. If not, who can I talk to or where should I learn?
Thank you both. Huge help!
no there isnt, but maybe we should consider something like that.Is there an area dedicated on this site to formula building methods/techniques.
For myself, my approach is to determine which pieces of info I need to resolve the question.
Then I (try and) figure out how to get each of those pieces of the puzzle (that is often the hard part)
I then fit each of the peices together into 1 formula - and then test
In your case, we needed to be able to use 3 things for the (column) search criteria, so my 1st thought was to combine them into 1 cell...
=B1&B2&B3
Once we have that, it comes down to using a pretty standard index/match for teh rest...
INDEX(Historical!$A:$E,MATCH('Prior YTD'!$A8,Historical!$A:$A,0),MATCH($B$4&$B$1&$B$2,Historical!$A$4:$E$4,0)))
Then add some error-trapping...
=IF(A8="","",INDEX(Historical!$A:$E,MATCH('Prior YTD'!$A8,Historical!$A:$A,0),MATCH($B$4&$B$1&$B$2,Historical!$A$4:$E$4,0)))
I guess what it comes down to is that you generally need to things (at least) to run a calc, but often, those 2 (or more) things are not inthe same format/context, so you need to convert 1 or the other - or sometimes both - to something similar that you can then compare or add or whatever.
Once I combined the 3 pieces of info on sheet 1, that gave me a starting point
There is no 1 standard 1-way-fits-all approach for this (at least, I dont thing so), but I hope that helps?
Just FYI, I have started a new thread here, concerning your question...
http://www.excelforum.com/tips-and-t...l-problem.html
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
pncarlson 
Register To Reply
Bookmarks