I have never done macros or VBA, so I am struggling with a question: I have a table as shown. Each day, a new column is filled in (for instance E2:E6 for today). For any product that sold, how do i get it to tell me so down in A11:A15. Then, when the next column gets data (tomorrow's data in column F), how do i get A11:A15 to go get the latest data from column F? Can this be done with formulas?Excel Question.JPG
Last edited by whittlemurf; 03-27-2018 at 02:37 PM.
Do not post multiple threads asking the same thing. A solution has been provided in the previous one. Thanks.
EDIT: That one wasn't your post, so do not ask question in other thread. Even so, you will find a solution there.
In cell A11, array-enter (enter using Ctrl-Shift-Enter) the formula
=IFERROR(INDEX($A:$A,SMALL(IF(INDEX($2:$6,,MAX(IF($B$2:$Z$6="YES",COLUMN($B$2:$Z$6))))="YES",ROW($A$2:$A$6)),ROW(A1))),"")
and copy down to A12:A15. If your dates could eventually extend past column Z then change the Zs to AZs or BZs....
Bernie Deitrick
Excel MVP 2000-2010
That's super - and easy. What if instead of "YES", the data in B2:Z6 was the actual product name (such as "banana")?Excel Question - 2.JPG
Try this, also array-entered:
=IFERROR(INDEX($A:$A,SMALL(IF(INDEX($2:$6,,MAX(IF($B$2:$Z$6<>"",COLUMN($B$2:$Z$6))))<>"",ROW($A$2:$A$6)),ROW(A1))),"")
That's works great. Thanks so much!!
Bernie, I have tried adapting what you sent for use in my actual spreadsheet, but I'm unsuccessful so far. I have attached a copy of my spreadsheet with my actual data. It's a bit backwards, but I enter a "1" for the current month in FR36:FR58 if i own that particular investment.
What i am trying to do is use your formula to consolidate that data for the current month into a list of the things i actually own (see the sample output down in A66:B70).
The formula i am using is: =IFERROR(INDEX($C:$C,SMALL(IF(INDEX(36:59,,MAX(IF($D$36:$ZN$59<>"",COLUMN($D$36:$ZN$59))))<>"",ROW($C$36:$C$59)),ROW(C35))),"")
Thanks for looking at this again!!Excel Question - 3.xlsxExcel Question - 3.JPG
The error that you made in adapting the formula was updating the cell reference passed to ROW( ) to C35 - that would return the 35th smallest row number that is filled in (which does not exist).
Array-enter this instead (using Ctrl-Shift-Enter)
=IFERROR(INDEX(B:B,SMALL(IF(INDEX($36:$59,,MAX(IF($D$36:$ZN$59<>"",COLUMN($D$36:$ZN$59))))<>"",ROW($C$36:$C$59)),ROW(A1))),"")
Then copy down and across one column if you also want the values from column C (your example returned the values from B, not C) - your picture and your workbook differ as to which column's values are returned, so I went with your workbook.
You are awesome - thanks a ton!
You're welcome - happy ot hear you got it all worked out!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks