samyscraps01
Board Regular
- Joined
- Jul 6, 2017
- Messages
- 58
Hi all, i'm new to excel and I'm having significant problems fixing my issue. I need to find the amount in column H (titled Actual) based on three types of criteria.
1. I found it easier to concatenate data on column A. That includes two of my criteria, which is Category, and the Acct. No. derived from columns C and G. More specifically in this case, I am looking at just "Transfer In" from the Category-C Column.
2. The third criteria is where it gets complicated. I have two drop down lists on B19 and C19 that contain date ranges. (The dropdown lists are disabled but you get the idea, the date ranges will fluctuate based on the user selection). The date ranges in each list are identical and are derived from a separate report not shown in this spreadsheet. This allows the user to choose the date range of their choice. The idea is that when I change cell A19 (it should be dropdown list with more account numbers but for now, I am just using one account number in that cell), it will change cell B18.
3. The purpose of doing is that I need to find what the actual amount of money was transferred in. I am using the account number, the category and of course the date to determine that. The problem is that when I use only one date, so, say the date in B19. I will get that exact date. If i'm a user I will only get a response in cell C22 (highlighted yellow) if I happen to know the date that transaction of the transfer in occurred, or I select it at random. I don't want this, I want to know that if the user selected a date range and the transaction occurred within that range, it should display it on C22. I used the formula below and it works well but of course it's only looking at cell B18 and cell B18 only includes one date, the date on cell B19. Can anyone help with this? I was trying to upload a spreadsheet but for some reason this site makes it extremely difficult to upload anything. I couldn't even get a jpeg on here.
=IFERROR(INDEX($H$2:$H$13,SMALL(IF($B$18=$A$2:$A$13,ROW($A$2:$A$13)-MIN(ROW($A$2:$A$13))+1,""),ROW(A1))),"")
Thank you.
Samy
1. I found it easier to concatenate data on column A. That includes two of my criteria, which is Category, and the Acct. No. derived from columns C and G. More specifically in this case, I am looking at just "Transfer In" from the Category-C Column.
2. The third criteria is where it gets complicated. I have two drop down lists on B19 and C19 that contain date ranges. (The dropdown lists are disabled but you get the idea, the date ranges will fluctuate based on the user selection). The date ranges in each list are identical and are derived from a separate report not shown in this spreadsheet. This allows the user to choose the date range of their choice. The idea is that when I change cell A19 (it should be dropdown list with more account numbers but for now, I am just using one account number in that cell), it will change cell B18.
3. The purpose of doing is that I need to find what the actual amount of money was transferred in. I am using the account number, the category and of course the date to determine that. The problem is that when I use only one date, so, say the date in B19. I will get that exact date. If i'm a user I will only get a response in cell C22 (highlighted yellow) if I happen to know the date that transaction of the transfer in occurred, or I select it at random. I don't want this, I want to know that if the user selected a date range and the transaction occurred within that range, it should display it on C22. I used the formula below and it works well but of course it's only looking at cell B18 and cell B18 only includes one date, the date on cell B19. Can anyone help with this? I was trying to upload a spreadsheet but for some reason this site makes it extremely difficult to upload anything. I couldn't even get a jpeg on here.
=IFERROR(INDEX($H$2:$H$13,SMALL(IF($B$18=$A$2:$A$13,ROW($A$2:$A$13)-MIN(ROW($A$2:$A$13))+1,""),ROW(A1))),"")
Thank you.
Samy
