How to make a range of cells react to changes in one cell

**eleonore** · 05-20-2009, 05:55 PM

Crossposted from here, with some editing to the below version to clarify what I'm trying to do. I hope that this is OK, I'm new!

Hello,

There has got to be a name and a formula to do what I'm trying to do, I just know it! Hopefully one of you smart, helpful folks will be able to assist me.

I am creating a budget proposal with various ranges of data, and wanting to have some cells that react to certain changes. So, for example on total food cost, instead of having to create multiple row sections of how much things would cost for 150 people, 200 people, etc, I would like to have a drop-down list in a cell titled "number of guests," and then have my range of expense rows, "Breakfast," "Lunch" etc., change to reflect the number of
guests (or whatever) being chosen. (My columns are "anticipated budget," "actual," and "Difference," in case that matters.)

I hope I'm making sense! Is this something that is even possible to create?

Thank you for any help at all!

**Paul** · 05-20-2009, 08:47 PM

Hello Eleonore, and welcome to the forum.

The response posted on the other site might help you create the drop-down lists (although it may also complicate things), but it won't help you in updating all of the other cells.

There are two ways to update other cells' values than the one you just changed. One is using Excel functions (formulas) like =A1*B1*.05 and the other is using macros.

Depending on how you envision the layout of your spreadsheet will likely determine the best solution. Would you be able to upload an example worksheet to the thread, and show what you currently have and expected results? Add any notes you think may be relevant, and any restrictions that you know of.

**eleonore** · 05-21-2009, 07:14 PM

Hey!

I think I figured out what I wanted! It's not the most elegant way and there's probably something smoother out there, which I would be really happy to hear about, but I've attached what I came up with.

Basically, I made a bunch of formulas that reference different worksheets. Uh. I guess I don't really know how best to explain. Um.

In the main worksheet in a cell that I wanted to be variable, I made a formula that references a cell on another worksheet. In that second worksheet, I made those cells reference an "input" cell that I color-coded on the main worksheet. I couldn't just use the "input" cell on the main worksheet, because...I don't know why. I just made it that way, ha. You could probably do the same just by making an "input" cell right on your main worksheet.

Anyway, hope that helps others! And if anyone comes up with anything smarter, let me know!