I have "table1" with dates of 4 months from Nov/2024 to Feb/2025 in the top row starting in A2 (i.e. B2=Nov/1/2024, C2=Nov/2/2024, D2=Nov/3/2024 ... etc.). In the same "table1", the first column lists a bunch of names (A2=John, A3=Dave, A4=Mary .... etc.). The table contains the account balances for each person for each day in the 4 months. For example, the second row lists John's account balances everyday (including weekends) in the 4 months across row #2.
The same workbook has "sheet1" that contains the following amongst all the columns:
"M" = names (e.g. Dave, John, Mary)
"W" = random dates within the 4 months Nov/2024 to Feb/2025 (e.g. Jan/07/2025, Nov/20/2024, Feb/21/2025)
"Z" = whole numbers from 0 to 160
I'm trying to build a complicated formula in column AA for sheet1 to meet the following:
1) Use the name in "M" and date in "W" to find the corresponding value in "table1" (e.g. M2="John" and W2=Nov/05/2024, retrieve account balance for John on Nov/05/2024 in table1 and place in cell AA2)
2) Use the value in "Z" and repeat the above to fill the adjacent "Z less 1" cells. (e.g. using same example above, if Z2=4, then pull the account balance for John for 3 (i.e. 4-1) dates from Nov/6/2024 & Nov/7/2024 & Nov/8/2024 and place the values in cells AB2 & AC2 & AD2). *** in other words, "Z" is the total number of days we need the balances.
I'm hoping to avoid using macro, so I think some type of IF/THEN logic could work to satisfy #2 above by copying the formula from AA to fill the rest of each row so that in the above example of Z2=4, the formula will retrieve values to place in AA2 to AD2 and then with "N/A" beyond AD2.
After trying for weeks with no luck, I have come to realize this is well beyond my limited Excel skills, so I'm hoping someone can help me accomplish this.
The same workbook has "sheet1" that contains the following amongst all the columns:
"M" = names (e.g. Dave, John, Mary)
"W" = random dates within the 4 months Nov/2024 to Feb/2025 (e.g. Jan/07/2025, Nov/20/2024, Feb/21/2025)
"Z" = whole numbers from 0 to 160
I'm trying to build a complicated formula in column AA for sheet1 to meet the following:
1) Use the name in "M" and date in "W" to find the corresponding value in "table1" (e.g. M2="John" and W2=Nov/05/2024, retrieve account balance for John on Nov/05/2024 in table1 and place in cell AA2)
2) Use the value in "Z" and repeat the above to fill the adjacent "Z less 1" cells. (e.g. using same example above, if Z2=4, then pull the account balance for John for 3 (i.e. 4-1) dates from Nov/6/2024 & Nov/7/2024 & Nov/8/2024 and place the values in cells AB2 & AC2 & AD2). *** in other words, "Z" is the total number of days we need the balances.
I'm hoping to avoid using macro, so I think some type of IF/THEN logic could work to satisfy #2 above by copying the formula from AA to fill the rest of each row so that in the above example of Z2=4, the formula will retrieve values to place in AA2 to AD2 and then with "N/A" beyond AD2.
After trying for weeks with no luck, I have come to realize this is well beyond my limited Excel skills, so I'm hoping someone can help me accomplish this.