CODEWITHSUNDEEP
vbaexcel
Dendera
Dendera

Reputation: 19

Dynamic calculation in Excel based on a cell value

I've got a challenge in Excel i hope you guys can solve for me.

I have a drop down list (weeks) where i select the week 2015-18 to 2016-17. (nr 1 in picture)

In the cell called LY (nr 2 in picture) I want that to type the result of a dynamic sum range, based on the weeks input.

Picture of setup of the text above

The calculation logic is:

Picture of the setup of the logic values

I really hope you can solve this for me. I've got more rows to calculate, so if you could come up with a "global" code that works for that, it would be great

Upvotes: 0

Views: 1918

Answers (1)

Henrik
Henrik

Reputation: 2229

ok, so you do not need VBA for this.....

for the sake of easier updates; mark the weekrange, right click them and give them a name, with Define name

I called it yearlyweeks

The two drop downs lists, will contain the value corresponding to the text indicating that week.

So we can use this to get a cells address by value (if that text is unique in the range named yearlyweeks):

=ADDRESS(MATCH(H8,yearlyweeks, 0), 2)

where H8 is the cell address of a dropdownlist cell. 2 is the column index of the "B" column

this will result in something like:

$B$2

where the 2 indicates the relative row index, in the range, not the absolute row number.

we are really not interested in the column, or relative row, only the absolute row, but so far we can live with relative..:

=ROW(INDIRECT(ADDRESS(MATCH(H8,yearlyweeks, 0), 2)))

this will give you the relative row of the cell coresponding to what you selected on the drop downlist for one of the lists. So I would do this in two calculation cells, just to avoid the next piece getting too long..

lets let the calculation cells be in I10, and J10..

  • I10 hold the relative row for the beginning week
  • J10 for the ending week

below create a new set of cells with:

="F"&(I10 + 7)

and

="G"&(J10 + 7)

where 7 is the row offset of yearlyweeks

in yet one more cell J12 we make the range string:

=CONCATENATE(I11;":";J11)

and then does the calculation in the final cell:

=SUM(INDIRECT(J12))

Now I can do this as a one liner, but you really would hate that

Upvotes: 0

Related Questions