Row() Column() convert as Cell Reference in Formula

[Dahlia]

Hi All the Experts,

I have been cracking my head for couple of months already to do this but still fail. Am not good in explaining in writing..but I've not much choice, so here it goes..

I need to be able to calculate no. of leaves taken by each staff per month which supposed to be breakdown to:-

1. No. of leaves taken before the last 5 working days of the month
2. No. of leaves taken within the last 5 working days of the month

Based on lots of good examples and templates in the internet with some modifications applied, I managed to create a dynamic template which is like a table with all the staff name on the first column while the rest of the columns (header only) are filled with calendar dates which will automatically change based on the month we choose in drop-down list in cell B4. Next to those dates are columns to count total leave per leave type and overall total per staff. Then conditional formatting are applied to color those non-working dates/PH based on the list of event dates we entered manually next to the table.

Since our working dates are often change without prior notice (because it depends on the balance target to hit by EOM), so I use formula

=WORKDAY.INTL(<EOM date>,-5,"0000000",[holidays])

to identify which date would be the starting date for the last 5 working days of the month to calculate the leaves correctly based on 2 categories mentioned earlier. However, my issue now is after I get the date, I can use a formula to retrieve the column no. plus with the row no. of the staff name. And this row and col info should be as cell reference for me to insert in formula

=COUNTIF(E7:<cell ref from col row>,">=EOM date").

To further clarify, I cannot set a fix cell as ref because the last 5 working days' start date will vary based on the EOM date and our frequent changes of working days. So, each time, using formulas, the template currently able to auto-calculate no. of working days of the month, so should also be able to auto-calculate no. of leaves before and within the last 5 working days as well for each staff when changes made in the list of events.

After I get the info on row and column, I need to convert it as cell ref but so far I couldn't find the example to be able to do this automatically. For instance, i got AA7 as the cell ref (with row col formula and index), I want this automatically become cell ref to the formula above to become =COUNTIF(E7:AA7,">=EOM date") so that the formula can return to me values of how many leaves from date highlight in cell E1 till AA1 (considered that AA1 is the starting date of the last 5 working days of the month) for the staff name in row 7.

I really, really desperate to get expertise help in this complicated calculation.


Thank you in advance.

[vlady]

try it with the indirect
=COUNTIF(INDIRECT("E7:"&yourformulatogetrow/column),">=EOM date")

[Dahlia]

try it with the indirect
=COUNTIF(INDIRECT("E7:"&yourformulatogetrow/column),">=EOM date")

Hi vlady,

Wow! So grateful with your prompt response. It works perfectly! It also has given me the way to make the start range also to be dynamically change based on row:-

=COUNTIF(INDIRECT("E"&ROW(INDEX($B$8:$AI$30,MATCH($B10,$B$8:$B$30,0),MATCH($E$8,$B$8:$AI$8,0)))&":"&SUBSTITUTE(ADDRESS(1,COLUMN(INDEX($B$8:$AI$30,MATCH($B10,$B$8:$B$30,0),MATCH($AE$5,$B$8:$AI$8,0))),4),"1","")&ROW(INDEX($B$8:$AI$30,MATCH($B10,$B$8:$B$30,0),MATCH($AE$5,$B$8:$AI$8,0)))),"<>")

Yeayy!! Never expected that the solution is just the command INDIRECT and followed by the "" symbols. Soooo simple but yet it really differ us to the knowledgeable person. Tqvm vlady! Such intelligent! :-)

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.