Array with fixed source range

Hi,

I am trying to get an array formula (in tab 2 in example file) to return a part of a source table only (tab 1 in example file). I wanted to return just the month of May, which comes from rows 15-21 in the source data), but in a way that allows me to insert rows above, below or within the source table (anywhere in the year) without having to change the array manually each time. This would allow me to have several variations of the report created without manually re-typing all of the array formulas each time I made a change in the source data 9there are quite a lot of them).

In the example spreadsheet attached, in tab 2, the table of array formulas does accomplish this when the changes to the source data are within the desired Month (May), or after it (by addressing the range of rows numbered 15-21 of the source data). I do have to manually refresh the array by dragging the row of formulas in the first row of the array table down each time a change occurs (to recreate the array including the new rows). However, If I insert new rows in months before the target month (in April for example, which is above row 15 of the source data), the month of May will then start further down than row 15. This means that the range of rows used in the array formula will no longer be correct (May might then occupy rows 17-23 for example).

I was hoping that it was somehow possible to have the array refer to a fixed part of a table in the source data so that changes in the source data were possible. I tried using the 'INDIRECT' function when addressing the array formula, and it did work (it locks the array to the original source cells even if new rows are added), but I was not confident that the INDIRECT function was the right approach (not sure how long it would link to the original cells, and I would have no way to check over time where the original cell was).

Then I wondered if the array should refer to a named range for the month of May, which would presumably move with the month of May as new rows were added any where in the source data. I tried this, but I am not familiar with named ranges, and it did not work for me as part of the array though I may not have used the named ranges correctly).

Any suggestions would be appreciated.

Many thanks

That's how I understood you. I added K2, K3, K4 and you can change the year, month and category.
Look at the attachment.
Greetings.

In I7 of Expense cat 4 for May control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF('All Purchases'!$G$7:$G$29=4;IF(ISNUMBER('All Purchases'!$A$7:$A$29);IF('All Purchases'!$A$7:$A$29-DAY('All Purchases'!$A$7:$A$29)+1=(1&$A$6)+0;ROW('All Purchases'!$A$7:$A$29)-ROW('All Purchases'!$A$7)+1)));ROWS($1:1));"")

In A7 just enter, copy across, and down:

=IF($I7="";"";INDEX('All Purchases'!$A$7:$J$29;$I7;MATCH(A$4;'All Purchases'!$A$4:$J$4;0)))

Note 1. If you are on an American system, substitute a comma for a semicolon. Format also the formula cells the same way as the source cells.

Originally Posted by nunez100

Thank you for this Aladin Akyurek. I can't get the first formula to return anything when I add it to I7, and use ctrl+shift+enter. I have tried to check over it for mistakes, but I don't fully understand the formula to know what to look for. Maybe you wouldn't mind having a second look?

See the attachment...

This is great Glenn. Thank you very much.

I wonder if it would be possible to adapt your formula to reference some input boxes beside the final table? This way I could type the start and finish date, and the expense category to create different permutations of the report (or have a formula refer to somewhere earlier in the sheet). It would be really ideal to be able to include more than one expense category at once if possible (ie. 4, 5 and 6), but maybe this is asking too much.

Thank you again for your help, I was struggling to figure this one out myself.

Many thanks

George

Glenn,

That is amazing, thank you so much. I see you are also based in Norn Iron!

I will mark this as solved. One last question before I mark it solved: I tried to modify the formula to allow me to select several categories at once (4,5 and 7 for example), but i couldn't manage it (in my attempt, it just returned a blank). Do you think it is practical or feasible to do this, or would it make the formula too long / make the sheet volatile?

If you don't thin that it is practical, I will go ahead and mark this solved. I really appreciate the help.

Many thanks

George

Brilliant, that's amazing. It would be a maximum of 15 categories, but less would do if that is not practical.

I'm out in the sticks in Fermanagh, but I lived in the big smoke for about 10 years.

Hi Glenn,

Yeah Fermanagh is nice, when it's not raining.

Thank you very much again for this - it is excellent!

One tiny glitch I found when trying it out was that if I put a 1 in the category box, it seems to pick up category 1 and also category 10 as well (because 10 also has a 1 in it). I tried it on my main spreadsheet and the same thing happens - a 1 will pick up 1 and 10, 11, 12, 15 etc. A 5 will pick up 5 and 15.

As it is, I would have to only use it with a maximum of 9 categories. Do you think this aspect of it is fixable, to allow it to distinguish between double digit categories?

Many thanks

George