Hello everyone, hope all is well. I will be making a presentation – automated gathering of info. I just can’t figure out how to make this happen.
The first spreadsheet would be the output, the date on the top are the ship dates. I have customers that are ordering 2 items irregularly, need to track their orders.
Output.JPG
2nd image is where to get the output:
Raw Data.JPG
Is there a simple formula for this? Any help would be greatly appreciated. Thanks in advance!
It would help if you attached a sample Excel workbook, rather than pictures of one.
To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.
Please note that the Paperclip icon does not work.
Pete
Hello Pete, thanks for this. Please reference the attached, these are actual spreadsheets I am working on.
The date is not in dd-mm-yy.
Hi. You'll need an array formula to do that:
Formula:
=IFERROR(INDEX('Raw Data.xlsx'!$H$2:$H$6,MATCH(1,('Raw Data.xlsx'!$A$2:$A$6=$A3)*('Raw Data.xlsx'!$E$2:$E$6=B$2)*('Raw Data.xlsx'!$D$2:$D$6=OFFSET($B$1,,2*INT((COLUMNS($B:B)-1)/2),,)),0)),"")
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
If you open both files in the same location, it should then work perfectly. Should you NOT open the data source, you will then need to modify the formula to include the full path:
Formula:=IFERROR(INDEX('C:\Users\Windows 8\Desktop\[Raw Data.xlsx]Raw Data'!$H$2:$H$6,MATCH(1,('C:\Users\Windows 8\Desktop\[Raw Data.xlsx]Raw Data'!$A$2:$A$6=$A3)*('C:\Users\Windows 8\Desktop\[Raw Data.xlsx]Raw Data'!$E$2:$E$6=B$2)*('C:\Users\Windows 8\Desktop\[Raw Data.xlsx]Raw Data'!$D$2:$D$6=OFFSET($B$1,,2*INT((COLUMNS($B:B)-1)/2),,)),0)),"")
which, in my case, is: C:\Users\Windows 8\Desktop\
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hi there Glenn, a million thanks. It makes my presentation easier. Thanks again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks