Excel: replace cell range with another range?

[destrekor]

I've got a combobox (ActiveX - so I could customize font size of drop down), and it fills in a linked cell with the choice (Eastern Time, Central Time, Mountain Time). I'm trying to make a formula that determines which choice was made, and based on that choice, fill in a certain range from another sheet (where I have all three sectioned off).

I've given all ranges defined names.

I'd like to do it with standard excel formula instead of leaning on VBA if at all possible.


Thanks!

 

[sweenish]

Pretty sure you'll want VBA.

You're already using an ActiveX dropdown, what a little behind the scenes code?

 

[destrekor]

Pretty sure you'll want VBA.

You're already using an ActiveX dropdown, what a little behind the scenes code?

Yeah, I guess... I was just hoping to use standard Excel logic.

 

[Binky]

There are always many ways to do something in excel, but this might work. If none of the three choices is there, it reports FALSE.

=IF($A$1="Eastern Time",1,IF($A$1="Central Time",2,IF($A$1="Mountain Time",3)))

A1 = source cell with Eastern Time / Central Time / Mountain Time
It reports back 1, 2, or 3 depending on the choice, just replace the 1/2/3 with your cell that you want to pull from the other sheet. Copy down to all relevant cells.

 

[PowerEngineer]

I think you can do what you describe without resorting to VBA by using the INDIRECT and ADDRESS functions.

 

[destrekor]

There are always many ways to do something in excel, but this might work. If none of the three choices is there, it reports FALSE.

=IF($A$1="Eastern Time",1,IF($A$1="Central Time",2,IF($A$1="Mountain Time",3)))

A1 = source cell with Eastern Time / Central Time / Mountain Time
It reports back 1, 2, or 3 depending on the choice, just replace the 1/2/3 with your cell that you want to pull from the other sheet. Copy down to all relevant cells.

So each cell would have to have the formula. hmm

I think you can do what you describe without resorting to VBA by using the INDIRECT and ADDRESS functions.

How would I do this?

I'm not an Excel pro by any means.

 

[PowerEngineer]

How would I do this?

I'm not an Excel pro by any means.

Most of us that use Excel can't claim to be either; we just learn enough (usually by trial and error) to get by.

Looking at it a little more closely, you can do what you want through cell formulas using INDIRECT and INDEX. You'll want to read up on these, but the gist of it is that if you have the name of the range you want to use in A1, then INDIRECT(A1) will provide the reference you need to that named array. The INDEX function allows you to specify (by row and column numbers) a particular element in the named array. So, as an example =INDEX(INDIRECT(A1),3,1) will put whatever is in the third row and first column of the array that's named in A1 into the cell. You'll obviously need to change the INDEX row and column numbers to fill in the entire range.

Should be easy from here...

 

[destrekor]

Most of us that use Excel can't claim to be either; we just learn enough (usually by trial and error) to get by.

Looking at it a little more closely, you can do what you want through cell formulas using INDIRECT and INDEX. You'll want to read up on these, but the gist of it is that if you have the name of the range you want to use in A1, then INDIRECT(A1) will provide the reference you need to that named array. The INDEX function allows you to specify (by row and column numbers) a particular element in the named array. So, as an example =INDEX(INDIRECT(A1),3,1) will put whatever is in the third row and first column of the array that's named in A1 into the cell. You'll obviously need to change the INDEX row and column numbers to fill in the entire range.

Should be easy from here...

Does this need to be a formula in every cell that needs replaced? Or can it be done it one fell swoop, with one statement replacing an entire range of cells?

I tried referencing the named range, and then when I selected all of the cells within the range, it just referenced the name range again.

So my statement looked like this:
=IF($A$1="Central Time", INDEX(INDIRECT(Central_time(Central_time))))

I started playing around with it, and now have:
=IF($A$1="Central Time", INDEX(INDIRECT(Central_time),Sheet1!1:340,Sheet1!E:G))


I'll just put it this way: I have no idea what I'm doing.


I have cell A1 (a merged cell, does this matter?) that is my reference.
A2:C341 is a range named Time_block.
That range is where the weekly schedule times are displayed in half-hour increments for each week.

On Sheet3, I have three ranges, Eastern_time, Central_time, and Mountain_time, which are located at A1:C340, E1:G340, I1:K340, respectively.

Based on the value of A1 on Sheet1, I want to replace Time_block with either Eastern_time, Central_time, or Mountain_time.

Does that help indicate the direction we need to go? I can't for the life of me figure it out.

 

[PowerEngineer]

I have cell A1 (a merged cell, does this matter?) that is my reference.
A2:C341 is a range named Time_block.
That range is where the weekly schedule times are displayed in half-hour increments for each week.

On Sheet3, I have three ranges, Eastern_time, Central_time, and Mountain_time, which are located at A1:C340, E1:G340, I1:K340, respectively.

Based on the value of A1 on Sheet1, I want to replace Time_block with either Eastern_time, Central_time, or Mountain_time.

Does that help indicate the direction we need to go? I can't for the life of me figure it out.

Okay, let's take a different approach to this.

Here's a formula to put into the upper left hand cell of Time_block (i.e. A2):

=IF($A$1="Eastern_time",'Sheet3'!A1,IF($A$1="Central_time",'Sheet3'!E1,'Sheet3'!I1))

Copy/Paste this formula into all the cells in Time_block.

This works by having the formula in each cell grab its counterpart from the range you select. There are surely more elegant ways to do this, but this should work for you.

 

[destrekor]

Okay, let's take a different approach to this.

Here's a formula to put into the upper left hand cell of Time_block (i.e. A2):

=IF($A$1="Eastern_time",'Sheet3'!A1,IF($A$1="Central_time",'Sheet3'!E1,'Sheet3'!I1))

Copy/Paste this formula into all the cells in Time_block.

This works by having the formula in each cell grab its counterpart from the range you select. There are surely more elegant ways to do this, but this should work for you.

That works, except it has to be "Eastern Time", etc, and not "Eastern_time", of which the latter is the defined name of the range, whereas the former is simply a text string. So, the defined names are rather pointless in this instance.

I suppose getting my hands a little dirty with them now is practice for what I have in mind for this schedule: scrap this junk, and create a more interactive and far, far easier to change schedule using dynamic ranges and tables, creating a little mini scheduling program.

 

[Tweak155]

Sounds like VBA is a good choice, it really isn't all too difficult. If you want dynamic ranges, you'll probably have little choice in the matter.

 

[destrekor]

Sounds like VBA is a good choice, it really isn't all too difficult. If you want dynamic ranges, you'll probably have little choice in the matter.

Isn't all too difficult. Ha!

I'm very much a novice at any coding/scripting. I've taught myself a little here and there, but for the longest while I shied away from ever even thinking about getting a better grasp on the fundamentals.

Now, I find myself increasingly taking on these challenges (even if they are pitifully easy by some standards) instead of just letting the status quo be the status quo. Every little mini project gives me a chance to show myself more to employers and build my skills up.