Using index and match from another source excel file

[jjk1]

Using index and match from another source excel file. Search source list for rows that match lookup value and return rows for days in worksheet of specified month plus the last day of previous month that included in the list. This works for the specified month. I do not know how to include the last day of previous month that included in the list. Please help. Also, I have to enter the specified month as a number. I have tried to modify formula so that I can use the word for the month instead, but cannot get that to work. Please help.

=LET( data, '[AccountValues-Year2024SSG.xlsx]Year-24 SSG'!A:L, D3Match, FILTER(data, '[AccountValues-Year2024SSG.xlsx]Year-24 SSG'!B:B=D3), monthMatch, FILTER(D3Match, MONTH(INDEX(D3Match,0,1))=E3), yearMatch, FILTER(monthMatch, YEAR(INDEX(monthMatch,0,1))=F3), IF(ROWS(yearMatch)>0, yearMatch, "No matches found") )

This does not work when I tried to use month names.
=IFERROR( FILTER( '[AccountValues-Year2024SSG.xlsx]Year-24 SSG'!A:L, ('[AccountValues-Year2024SSG.xlsx]Year-24 SSG'!B:B=D3) * (MONTH('[AccountValues-Year2024SSG.xlsx]Year-24 SSG'!A:A)=MONTH(DATE(F3,MATCH(E3,{"January","February","March","April","May","June","July","August","September","October","November","December"},0),1))) ), "No matches found" )

So 2 questions: 1. the previous month question, and 2. the month word question. I don't have the knowledge to use VBA solutions.
Thank you, Jim

 

[Peter_SSs]

1. the previous month question

Try

=LET(data,'[AccountValues-Year2024SSG.xlsx]Year-24 SSG'!A:L,D3Match,FILTER(data, '[AccountValues-Year2024SSG.xlsx]Year-24 SSG'!B:B=D3),dates,INDEX(D3Match,0,1),FILTER(D3Match,(TEXT(dates,"myyyy")=E3&F3)+(dates=DATE(F3,E3,0)),"No matches found"))

2. the month word question

Assuming the month name is in E3 instead of the month number, try

=LET(data,'[AccountValues-Year2024SSG.xlsx]Year-24 SSG'!A:L,D3Match,FILTER(data, '[AccountValues-Year2024SSG.xlsx]Year-24 SSG'!B:B=D3),dates,INDEX(D3Match,0,1),FILTER(D3Match,(TEXT(dates,"mmmmyyyy")=E3&F3)+(dates=DATEVALUE(1&E3&F3)-1),"No matches found"))

If these do not do what you want then could we have a small set of sample data from the '[AccountValues-Year2024SSG.xlsx]Year-24 SSG' sheet (say 20 rows with some relevant dates in column A and relevant values in column B. Columns A, B & C should do).
Could we also see what is in D3:F3 of the formula sheet.
Both of the above with XL2BB would help greatly. (If you have trouble with XL2BB review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page linked above.)
Then explain which rows should be returned and why.

 

[jjk1]

Try

=LET(data,'[AccountValues-Year2024SSG.xlsx]Year-24 SSG'!A:L,D3Match,FILTER(data, '[AccountValues-Year2024SSG.xlsx]Year-24 SSG'!B:B=D3),dates,INDEX(D3Match,0,1),FILTER(D3Match,(TEXT(dates,"myyyy")=E3&F3)+(dates=DATE(F3,E3,0)),"No matches found"))

Assuming the month name is in E3 instead of the month number, try

=LET(data,'[AccountValues-Year2024SSG.xlsx]Year-24 SSG'!A:L,D3Match,FILTER(data, '[AccountValues-Year2024SSG.xlsx]Year-24 SSG'!B:B=D3),dates,INDEX(D3Match,0,1),FILTER(D3Match,(TEXT(dates,"mmmmyyyy")=E3&F3)+(dates=DATEVALUE(1&E3&F3)-1),"No matches found"))

If these do not do what you want then could we have a small set of sample data from the '[AccountValues-Year2024SSG.xlsx]Year-24 SSG' sheet (say 20 rows with some relevant dates in column A and relevant values in column B. Columns A, B & C should do).
Could we also see what is in D3:F3 of the formula sheet.
Both of the above with XL2BB would help greatly. (If you have trouble with XL2BB review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page linked above.)
Then explain which rows should be returned and why.

Thank you soooo much Peter!! It works perfectly! There is one other change I would like to add if possible. In the modified formula below (that you made work), I would like to plug in the file name and worksheet name in to a cell and reference in the formula instead of typing it into the formula, that way it can be easily changed.

so for the formula:
=LET(data,'[AccountValues-Year2024SSG.xlsx]Year-24 SSG'!A:L,D3Match,FILTER(data, '[AccountValues-Year2024SSG.xlsx]Year-24 SSG'!B:B=D3),dates,INDEX(D3Match,0,1),FILTER(D3Match,(TEXT(dates,"mmmmyyyy")=E3&F3)+(dates=DATEVALUE(1&E3&F3)-1),"No matches found"))

I would like the file name and worksheet to be in cell C3 for example. Then I can update cell C3 with a new file as needed. Is there a way to do this?

Separate question; are you good with formatting pie charts?
1. I have an excel file that builds pie charts. It auto populates the slice name and % portion correctly, but if its a longer name it extends the name to 2 lines, I want it to slice name to expand the text box and just be one line. It already correctly inserts the % portion below it.

2. Second issue if possible, can it be made to space out the text boxes automatically so that if its a tight space they are not on top of one another?

Thank you, Jim

 

[Peter_SSs]

I would like the file name and worksheet to be in cell C3 for example.

Can you show me (for the file name and worksheet name we have been using) exactly what you envisage being in C3?

Note that to do what you want will require use of the volatile function INDIRECT. Do you see that as a potential performance issue?

Separate question; are you good with formatting pie charts?

No. Definitely not my field.

 

[jjk1]

Can you show me (for the file name and worksheet name we have been using) exactly what you envisage being in C3?

Note that to do what you want will require use of the volatile function INDIRECT. Do you see that as a potential performance issue?


No. Definitely not my field.

The entry into cell C3 would be; '[AccountValues-Year2024SSG.xlsx]Year-24 SSG'
Then each year the entry would be updated. So in 2025 I would type in, '[AccountValues-Year2025SSG.xlsx]Year-25 SSG'
However, better yet, if it were possible to substitute the entry for (the year portion) within a C3 to a different cell reference where the year can be change more cleanly than typing in the name this would be ideal. So if you feel there would be a way to make that work, maybe in B3 I would type in 2025 and it would update the file and worksheet names. Then the C3 entry would be something like '[AccountValues-YearB3SSG.xlsx]Year-B3 SSG' . The worksheet name would only capture the last 2 digits.

For context: In the workbook this will be used in, it will entered into between 3 and 21 different grids in different worksheets I have set up in the file. Then in each formula that's plugged into a difference grid, I will change referenced cell D3 to say D4, D5, D6, etc. because these will contain different account numbers.

A problem: Once all of these formulas have been run and the data is populated into the cells I would like to save the workbook so the data in the cells is just as if it was entered into the cells manually and all the links go away. The "don't update links" button doesn't accomplish this. This is an very important aspect for this project to work. As I am typing this out I realize an unintended consequence may be its impact on the primary cells with the formula that is launched.

As far as how each of these formulas would launch, the hope was to create a drop down box for each that would sit in a default position that was blank, then when ready to run, select the drop down and choose the formula and it would run.

Let me know if you need additional clarification.
Thank you, Jim

 

[Peter_SSs]

maybe in B3 I would type in 2025

Isn't the year already in F3? I am assuming it is.

The entry into cell C3 would be; '[AccountValues-Year2024SSG.xlsx]Year-24 SSG'

For the C3 entry, leave off the single quote mark at the start & end & generalise the year.
So C3 would be: [AccountValues-YearyyyySSG.xlsx]Year-yy SSG

The formula to try:

=LET(data,INDIRECT("'"&SUBSTITUTE(SUBSTITUTE(C3,"yyyy",F3),"yy",RIGHT(F3,2))&"'!A:L"),D3Match,FILTER(data,INDIRECT("'"&SUBSTITUTE(SUBSTITUTE(C3,"yyyy",F3),"yy",RIGHT(F3,2))&"'!B:B")=D3),dates,INDEX(D3Match,0,1),FILTER(D3Match,(TEXT(dates,"mmmmyyyy")=E3&F3)+(dates=DATEVALUE(1&E3&F3)-1),"No matches found"))

A problem: Once all of these formulas have been run and the data is populated into the cells I would like to save the workbook so the data in the cells is just as if it was entered into the cells manually and all the links go away.

You indicated earlier that vba was not an option. If that is the case you would need to manually select all the formula results (or their whole columns) and Copy/Paste Special (Values).

 

[jjk1]

The last formula results in a #REF! error.
I would also like to kindly ask if the following modification to the formula is possible.
The current formula begins populating the source data into the primary cell that contains the formula. Can this be modified so that the first row of data is 2 rows below the primary formula, within that same column?
Thank you, Jim

 

[Peter_SSs]

The last formula results in a #REF! error.

Is the [AccountValues-YearyyyySSG.xlsx]Year-yy SSG workbook open? It needs to be for INDIRECT to work. With both workbooks open, I do not get a #REF! error.

I would also like to kindly ask if the following modification to the formula is possible.

Are there more modifications to come? 'Formula creep' often leads to less-than-ideal formula construction. Best to have all the requirements up front so the best approach can be taken from the start.

Can this be modified so that the first row of data is 2 rows below the primary formula,

What is the purpose for that? If you are planning to later put something in those top two 'empty' rows, you would not be able to as they would still be used by this formula even though you don't see anything in the cells?
Why not just move the formula down two rows to where you do want the results to start?

 

[jjk1]

Is the [AccountValues-YearyyyySSG.xlsx]Year-yy SSG workbook open? It needs to be for INDIRECT to work. With both workbooks open, I do not get a #REF! error.



Are there more modifications to come? 'Formula creep' often leads to less-than-ideal formula construction. Best to have all the requirements up front so the best approach can be taken from the start.



What is the purpose for that? If you are planning to later put something in those top two 'empty' rows, you would not be able to as they would still be used by this formula even though you don't see anything in the cells?
Why not just move the formula down two rows to where you do want the results to start?

Thank you for identifying the problem. I did not have the source file open. It works.
I agree with your statement about formula creep. Unfortunately, since I do not have your experience, I am not able to see ahead as clearly as you do. I would like to be able to do that. Some of these issues do not become apparent until I see what the updated formula produces and how the results look. I apologize.

Regarding skipping 2 rows before populating the output. In each of the grids where I would paste the formula, the 2 rows being skipped contain have text and column headers that are necessary. You were saying they have to be empty, that's problematic. In the cell that contains the formula I would like to have a drop-down that the default position is blank and then I can select the formula in the drop down and it would run. I am assuming I could just paste it into the drop down when creating it.

Thank you, Jim

 

[jjk1]

Thank you for identifying the problem. I did not have the source file open. It works.