Excel ranges that automatically change # of rows

Water Cooler
#1

I want to make an Excel file that schedules events

In order to do this, I want my file to automatically adjust ranges of cells based on the number of days between events.

A1 = 6 (days)

CELL B1 = “X”, meaning we will have an event on that day.

I want C8 to somehow look at the cells B1:B7 and verify that something happened (in B1)

I want C9 to somehow look at the cells B2:B8 and verify that nothing happened in that range.

Here’s the tricky part…what happens if I want to automate the difference between the top and bottom of the date ranges? For example, what if I wanted the first range to be B1:[B1+6] or rather B1:(B6+A1)

#2

sounds like an if then formula

#3

You can use the INDIRECT function.

In another cell – D3, say – put =“B”&B6+A1. When B6 contains 2 and A1 contains 5 , D3 will contain B7.

Then in cell C8, put B1:INDIRECT(D3); this tells C8 that the beginning of the range is cell B1, and the end of the range is the cell cited in D3.

When A1 changes, D3 will change, and your range will change. Just like that.

#4

I’d probably use indirect

#5

So would I.

#6

dude is such an excel magician respect.

#7

You know, after seeing this I remembered that for some reason indirect took me a little to get used to how it worked. Figured I’d share the link I used to learn: http://www.cpearson.com/excel/indirect.htm

#8

I never heard of the indirect command until now. Thanks guys

https://support.office.com/en-us/article/INDIRECT-function-21f8bcfc-b174-4a50-9dc6-4dfb5b3361cd?CorrelationId=a193bda2-1be9-421d-88c6-7ffa76a809f2&ui=en-US&rs=en-US&ad=US