Index, Match and Address functions in a formula but how do incorporate a certain condition to extract only certain data?

1
votes

My Workbook has three sheets: "Wire Sheet", "Paste Data" sheet and "Wire instructions"

I need the "Wire sheet to dynamically populate with the data from the other two sheets based on certain conditions, i.e. date and data from that date.

I am using the index, match address functions to find the names from the "paste data" tab based on a specific but I can't figure out how to put a condition in there to only return names that are "buys"...

Here are my formulas:

This is just to find the cell:

S17= =MAX(IF('Paste Data'!B:B = 'Wire Sheet'!K2, ROW('Paste Data'!B:B) - ROW(INDEX('Paste Data'!B:B,1,1))+1))

S18= =ADDRESS(MATCH(K2,'Paste Data'!B:B,0),2,3,1,"Paste Data")&":"&ADDRESS(S17,5,3,1)

S19= =INDEX(S18,1)

here is the formula i am using to return the data: =IF(INDEX(INDIRECT(S19),1,4)=0,"",INDEX(INDIRECT(S19),1,4))

Example on how spreadsheet works:

Currently: if you place a specific date in cell K2 on the "wire sheet", the boxes (cells A18:O67) populate with the data from the other two sheets based on that specific date. The data coded "Sell" is being populated in the boxes on the wire sheet, but should not be.. here is an example:

For example: If you place the date 7/13/2018 in K2 cell, the cell B57 is populated with letters SCR, which shouldn't be the case, as if you go to the "paste data" sheet, this is a "sell".

enter image description here

Then if you place the date 7/16/2018 in K2 Cell -- the cell B18 is doing the same thing, its populated with WQ when it shouldn't be, as it is a "sell", on the "paste Data" sheet. I would like it to just skip over this row of data and go and find the next row that is coded "buy"..

enter image description here

I just want to place the data that are "buys", on the wire sheet. All the sells should be passed over. I am not sure how else to explain this, which might be part of the issue.

enter image description here

Thanks in advance.

https://my.pcloud.com/publink/show?code=XZNzcB7ZpIigcTAabUz2vpPaXQ4nSVUfdc2X

1
vbaexcelexcel-formula
Many people that would help do not download excel files; instead, post a screenshot of a useful part(s) of your sheet for us to easily look at – Marcucciboy2
Either put your code or formula into the post so we can review. – Cyril
please read minimal reproducible example. you need to provide a simpler example and output the acheives what you want. asking us to parse all that information is too broad. Keep it simple. – Scott Craner
Sorry I am trying to be as precise as possible. I am trying to upload some screen shots, but for some reason am not able too. – ap5
Make sure that you've saved them somewhere first - it will not upload directly from the clipboard – Marcucciboy2

1 Answers

0
votes

+-----[editRevision2]----+

My way :

Y5       =COUNTIF('Paste Data'!B:B,'Wire Sheet'!K2)

Z6       =IF((ROW(Z6)-5)<=Y$5,ROW(Z6)-5,"")
AA6       =INDEX('Paste Data'!F:F,MATCH('Wire Sheet'!$K$2,'Paste Data'!B:B,0)+'Wire Sheet'!Z6-1,1)
AB6       =IF(AA6="Buy",AB5+1,AB5)
AC6       =IF(AA6="Buy",INDEX('Paste Data'!E:E,MATCH('Wire Sheet'!$K$2,'Paste Data'!B:B,0)+'Wire Sheet'!Z6-1),"")
AD6       =IF(AA6="Buy",INDEX('Paste Data'!K:K,MATCH('Wire Sheet'!$K$2,'Paste Data'!B:B,0)+'Wire Sheet'!Z6-1),"")

select Z6:AD6 and extend to D15, then 

B18       =IFERROR(INDEX($AC:$AC,MATCH(A18,$AB:$AB,0)),"")
E19       =IFERROR(INDEX($AD:$AD,MATCH(A18,$AB:$AB,0)),"")

copy B18 to J18, B31, J31 ... and copy E20 to J20, B33, J33 ...

+-----[ProviousEdit]----+

Make

B18   =if(SUMIFS('Paste Data'!$K:$K,'Paste Data'!$E:$E,IF(INDEX(INDIRECT(S19),1,4)=0,"",INDEX(INDIRECT(S19),1,4)),'Paste Data'!$B:$B,$K$2,'Paste Data'!$F:$F,"Buy")=0,"",IF(INDEX(INDIRECT(S19),1,4)=0,"",INDEX(INDIRECT(S19),1,4)))

and adjust the INDEX(INDIRECT(S19),1,4) accordingly for J18,B31,J31 ...

Hope that solves.. (: