Forum Discussion
I really need a help with a formula!
I'm certain there's a better way.
But the best way (I strongly suspect) is likely to involve a redesign of the whole layout, probably separating the collection of data (the Input) from the Output.
Is it possible to post not just images but the actual Excel workbook? If it contains confidential or proprietary data, then post something without that confidential info, but as much of the rest as possible.
And could you say more about the "big picture" here....what are you tracking, how is Sheet 1 updated, how frequently does it change, do you need to track history or just the most recent status, etc?
Excel is really good with taking detailed "raw data" and extracting from it summary data, daily or weekly reports, etc. That is to say, the kind of things you appear to be doing "manually" in the form of copy and paste. There are formulas or functions for that. But they work better still if the underlying data is well designed for that purpose.
Hi,
Thanks for the reply.
Would be difficult to use without changing the layout of data?
Sheet 1 is updated based on other raw data. The cell highlighted in green refers to the shipping date and doesn't change. But, let's say Cell B4 shifts to C4. Then I need to fix the date in sheet 2. It frequently changes (every month) because of supply status. I don't need to track history but need to keep updating whenever the shipping date of the order shifts to different date.
Colinahn I agree with mathetes that there are still some questions and that it would be much more helpful if you uploaded a sample sheet. My interpretation of the info is that the invoice numbers are A001 for the first invoice for product A1 and then A002, A003, ... for that product and similar for B1, C1, and D1, but what if you have product A2? I also have same question about dates as mathletes since the dates shown on sheet 2 don't line up with dates on sheet 1. That said based on what I believe you ASKED for, here is an equation that will work:
=INDEX(FILTER(Sheet1!$B$3:$G$3,INDEX(Sheet1!$B$4:$G$7,MATCH(C2,Sheet1!$A$4:$A$7,0),0)>0),COUNTIF($C$2:C2,C2))you could even use a variation of this equation (or a more simplified lookup based on this date) to get the corresponding QTY just in case that might change on occasion.
Hi,
Thanks for the formula.
It works great!
Could you tell me the logic of this formula?
Since I only can use Excel on my work computer, I can't really upload a sample.
My personal laptop does not have Excel, and I have to purchase the subscription.
So, the only way I can think of is to take a screenshot.
Colinahn Sure. To start, the basic concept is that it filters the dates to only dates that have a quantity and then pick out those dates in order 1 by 1. So, I reformatted the formula into multiple lines to ref the lines in the explanation
=INDEX( FILTER(Sheet1!$B$3:$G$3, INDEX(Sheet1!$B$4:$G$7, MATCH(C2,Sheet1!$A$4:$A$7,0) ,0) >0), COUNTIF($C$2:C2,C2))so this starts with an INDEX (ln 1) we will skip for now and then a FILTER (ln 2), which is filtering the row of dates based on the criteria on lines 3-6. This criteria is based on an INDEX of the whole data range (ln3). The criteria for the index is a MATCH for the row (ln 4) and a 0 for the column (ln 5), which will return the whole row. The MATCH (ln 4) uses the code in C2 to choose which row in the original data table to look at. So now that the INDEX (ln 3-5) returns the proper row it then applies the >0 criteria (ln 6) so that an array of true/false values based which columns have values and this is used to FILTER the list of Dates. So now it selects which date using that first INDEX (ln 1) based on the this filtered list and choosing one based on the COUNTIF (ln 7). The COUNTIF counts how many occurrences of the code in C2 is in the list up to this line (i.e. first occurrence=1=first date, second occurrence=2=second date, etc...)
I hope that help break it down.
For the sake of @Colinahn, I hope your solution works.
You said My interpretation of the info is that the invoice numbers are A001 for the first invoice for product A1 and then A002, A003, ... for that product and similar for B1, C1, and D1, but what if you have product A2?
I agree with that assessment. The thing I couldn't figure out, though, was how those different invoice numbers (PO #s), which are only available in Sheet2, were to be correlated with data in Sheet 1, where they didn't appear.
I started to create my own version emulating yours, and quickly realized that one problem with what is currently shown is that the combinations on Sheet 2 are not unique. For example, product code A1 with quantity 300 appears multiple times. A lookup or match would not know which is the order that has changed.
But I also need to ask if the PO# is in any way reflected in Sheet 1. It may be the missing ingredient for the kind of automatic or formula based connection between the two sheets. The whole process is still a mystery (to me) in the sense that Sheet 1 is the source data for what you're trying to do to update Sheet 2, yet Sheet 2 has data (PO #) that doesn't appear (not visibly) in Sheet 1, yet that degree of granularity of data is what we need to do the update in Sheet 2.
Further questions:
- are there ever two or three orders of a given product in the same week?
- why are the dates in Sheet 2 different from those in Sheet 1? I mean that the Sheet 1 dates are seven days apart (a weekly schedule), but in Sheet 2, they're more scattered, more "random"....again, as if Sheet 2 is more "raw" data, more granular.
Again I'd ask if you could possibly upload a copy of the spreadsheets themselves.
As you can see from the questions above, though, there seems (to me at least) a need for more clarity on what data is coming from where.... How do YOU know which quantities or dates to change in Sheet 2 given the ambiguity of data on Sheet 1?
