Smart referral quotation E1:E8, replacing number 8 by cell value

[ThomasvRiet]

Hi all,
I work on a document to automatically track how many holiday days I have left.
In the gyazo link you can see the entire document.
https://gyazo.com/a0106151e3e9aba40f9e31ccca13158c

Simple workflow:
Cell B6 Determines the day of today
Cell C6 Determines the correlating weeknumber

In Column D, this week is automatically highlighted
In Column E you can see the days I build up
In Column G you can see the days I have taken off -> creating a number 1 in Column F

The balance in days is determined by adding all values (1's) in Column E minus all values (1's) of Column F from row 1 untill this week's row.
=SUM(E1:E8) - SUM(F1:F8)

What I would like to add now:
in this week (42), to values need to be added from E1:E8 since row 8 is the row of this weeknumber.
Instead of defining the quatation E1:E8 I would like to have it as following: E1:E('rownumber current week')
Cell B8 calculated this rownumber now using =MATCH(B7;D:D;0)

Is it possible to integrate this result of Cell B8/row number current week in any way?

Help is highy appreciated.

 

[jimrward]

Welcome,
Take a look at the indirect function, search for it on this forum, there was an example just last week