Am I Looking For Something That Doesn't Exist?

S

seanryann

I have a fairly complicated spreadsheet, with basic formulas, but man
cells that inter-relate with each other.

I am looking for a way in which I can simply create an option so tha
you type in a number into a cell off to the side of the main part o
the sheet(which would change, say, revenue), and it will show how i
affects another cell (say, return on investment).

I want to make the spreadsheet more user-friendly, so that outsid
users don't have to go into the main part of the sheet and ente
specific numbers to see how it affects other cells.

Does that make any sense? Is there a way to do such a thing?

Thanks for your help
 
D

davesexcel

Do you want a userform? Where a box pops up and there will be boxes o
the form tied into cells on your spreadsheet
 
S

seanryann

I'm honestly not sure. :confused:

If you think something like that would work best, than I can't argue
You have infinately more Excel knowledge than I
 
O

Otto Moehrbach

I don't know much about what you have, but it sounds like you could use a
cell reference or cell name in your formulas. Then when you change the
contents of that one cell, the change will be carried throughout your sheet.
This is commonly done. Does this help? Otto
 
S

seanryann

Otto, yes it does. Thank you.

Is there a web page that would help me to create cell references? I'm
unfamiliar with them.

Thanks again.
 
R

Ragdyer

In this case, a cell reference is created by simply replacing a value in a
formula with a cell address that contains a value.

For example, the formula:
=125+250

Can be revised to:
=A1+B1

Where you enter values in A1 and B1 to change the results of the formula
without changing the formula itself.

Carrying this a step further, say you have a formula for calculating sales
tax.
The sales amounts are in Column A, from A1 to A10.
In B1 you enter the formula:

=A1*0.0825

And copy it down to B10.
So that the formulas look like,
=A2*0.0825
=A3*0.0825
=A4*0.0825
.... etc.

BUT ..
say you now have to change the tax formula for customers in a different
city.

Instead of going through every formula and revising it, you could use a cell
reference for the sales tax and revise the formulas by changing the contents
of a single cell.

Say you use C1 for the tax rate.
Just enter
0.0825
into C1, and use this formula.

=A1*$C$1

You'll have to make C1 an *absolute* reference, so that it will *not* change
(increment), when you copy the formula down Column B.

=A2*$C$1
=A3*$C$1
=A4*$C$1
.... etc.

Does this help you understand using cell references?
 
S

seanryann

Ragdyer, thank you. It does indeed help.

I guess now that I know my options, it sounds like a "userbox" may b
the way to go, for my current situation.

I'll look into how to use them. I haven't created one before..
 
D

davesexcel

I guess now that I know my options, it sounds like a "userbox" may b
the way to go, for my current situation.

I'll look into how to use them. I haven't created one before...
--------------------------------------------------------------------
Hi,

What do you want to happen?

Somebody, enters some numbers and, the results show up,below or beside
whereever, but they do not have to see the entire worksheet, is tha
what we are looking at?
 
S

seanryann

Or is there a way I can have a dialogue box open up, and the user ca
enter numbers into it, and it changes specific numbers in th
spreadsheet? Then the pre-entered fuctions would take over from there
 
O

Otto Moehrbach

You can have a macro that displays an InputBox asking for whatever you want
to ask for. The same macro will then put that number (or whatever) in some
cell that is referenced in the formulas. Same thing as before except that
the macro asks for and puts the number in the cell in. HTH Otto
 
S

seanryann

Is there a way I can modify a cell right in the same spreadsheet, that
would manipulate a number that's already part of the spreadsheet, and
thus, the change other numbers that are connected to it via formulas?
 
Top