

Get a formula where the first and last rows of a lookup range are the value of 2 calculated cells, not manually typed in.

Michael O'Flaherty
Wellington

New Member
Members

Forum Posts: 2
Member Since:
December 20, 2022
December 20, 2022

- 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

Velouria
London or thereabouts
Member
Members

Trusted Members

Trusted Members

Forum Posts: 574
Member Since:
November 1, 2018
November 1, 2018


Michael O'Flaherty
Wellington

New Member
Members

Forum Posts: 2
Member Since:
December 20, 2022
December 20, 2022

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
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 170
Currently Online: Mynda Treacy, Riny van Eekelen, Ivan Kulubya, Duaa Elsheikh, Christian Huapaya, Laxmi Praveen
Guest(s) 30
Currently Browsing this Page:
1 Guest(s)
1 Guest(s)
Top Posters:
SunnyKow: 1431
Anders Sehlstedt: 845
Velouria: 574
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 213
A.Maurizio: 202
Aye Mu: 201
Jessica Stewart: 185
Newest Members:
Vicky Otosnika
Abhishek Singh
Kevin Sojourner
Kara Weiss
And Woox
Armani Quenga
moshood bello
annelies b
James1989
lucy gilmour
Forum Stats:
Groups: 3
Forums: 24
Topics: 6045
Posts: 26524
Member Stats:
Guest Posters: 49
Members: 31492
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Riny van Eekelen
© Simple:Press —
