Notifications
Clear all
General Excel Questions & Answers
4
Posts
3
Users
0
Reactions
113
Views
Topic starter
- In Excel 2019 I have 2 sheets; Category_P&L_CM and Monthly_Sheet.
- On sheet Category_P&L_CM column A has account names, column B has amounts for the current month and column C has the year to date amounts.
- The list of account names and their data starts at row 10, and the number of the last row varies depending on the activity in the accounts.
- The list of accounts is quite long and has a group for Northern Hills in it. This group starts where Northern Hills listed first in column A. Cell E3 has only the words Northern Hills.
- Cell F4 has the formula =MATCH(E3,A:A,0) which gives me the row number where Northern Hills first appears, in October this was row 100.
- Cell F7 has the formula {=MAX(ROW(A:C)*(A:C<>""))} which gives me the last row number where data appears, in October this was row 127.
- On Sheet Monthly_Sheet cells P2 and P3 have the the values from F4 and F7 on sheet Category_P&L_CM.
- The first row of my lookups is row 12.
- Cell P12 has the following formula: =IF(B12<>"","",IFNA(VLOOKUP($G12,'Category_P&L_ CM'!$A$100:$C$127,2,FALSE),0)).
- This formula applies to all cells below P12 in column P.
- Next month I will have to change the 100 and 127 to a different row numbers.
- How do I get the values of cells P2 and P3 to automatically define my lookup range, so I don't have to manually change the formulae each month?
- Thanks Michael
Posted : 22/12/2022 8:07 pm
May you post sample file
Posted : 23/12/2022 1:36 am
You could use:
=IF(B12<>"","",IFNA(VLOOKUP($G12,INDEX('Category_P&L_ CM'!$A:$A,$P$2):INDEX('Category_P&L_ CM'!$C:$C,$P$3),2,FALSE),0))
though I would probably define a name using those INDEX functions just to keep the formula more readable and easier to maintain.
Posted : 23/12/2022 5:20 am
Topic starter
Hi MGB and Velouria
Thanks for your comments.
I was working on this until just before Christmas, and have just got back on my computer.
I find myself rusty trying to resume where I was at a month ago so have decided to abandon this project.
Nevertheless I am grateful for your interest
Kind regards
Michael
Posted : 20/01/2023 8:24 pm
