I really need a help with a formula!

Question

Hi,I've been simply copying and pasting the value of date from sheet 1 (highlighted in green) to sheet 2 (highlighted in yellow) whenever the value of qty shifts to a different date.What I want to do is to look up the value of code and qty in sheet 2 and find the value of the date(Cell B2:B9) in sheet 2 from sheet 1.&nbsp;Sheet 1&nbsp;&nbsp;Sheet 2&nbsp;The problem is that qty. data in sheet 1 often moves to a different date (sometimes they get to ship out early and vice versa). Therefore, I need to copy and paste whenever it moves to a different date.Please help me if there is any better way rather than just copying and pasting it every time.Thank you!

mathetes · Answer

Colinahn&nbsp;&nbsp;I'm certain there's a better way.&nbsp;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.&nbsp;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.&nbsp;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?&nbsp;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.

colinahn · Answer

mathetes&nbsp;Hi,Thanks for the reply.&nbsp;Would be difficult to use without changing the layout of data?&nbsp;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.&nbsp;

mathetes · Answer

Colinahn&nbsp;&nbsp;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.&nbsp; A lookup or match would not know which is the order that has changed.&nbsp;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.&nbsp;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.&nbsp;Again I'd ask if you could possibly upload a copy of the spreadsheets themselves.&nbsp;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?&nbsp;&nbsp;

mtarler · Answer

Colinahn&nbsp; &nbsp;I agree with&nbsp;mathetes&nbsp;that there are still some questions and that it would be much more helpful if you uploaded a sample sheet.&nbsp; 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?&nbsp; 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.&nbsp; 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)&gt;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.&nbsp;

mathetes · Answer

mtarler&nbsp;&nbsp;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?&nbsp;&nbsp;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.&nbsp;&nbsp;&nbsp;&nbsp;

Forum Discussion

I really need a help with a formula!

7 Replies

Resources