Sending to instead of reteiving fom

[Serge]

On sheet 1 I have multiple entries with three specific critereas in each rows.
On sheet 2 there are 6 areas that require user input in each of the 6 areas
in order to sum up quantities that meet the tree specific critereas.
Is there a method of automatically sending data from sheet 1 to sheet 2 &
keeping count & adding as sheet 1 populates?
Thank you in advance.
Serge

 

[Bill Ridgeway]

No! You can pull data but you can't push data.

Regards.

Bill Ridgeway
Computer Solutions

 

[Serge]

Thanks for your reply Bill,
Would there be a way to memic this? Make it look like what you say "push
data".

 

[Bill Ridgeway]

Whether you push or pull data it comes to the same thing. I'm intrigued by
why you want to push data instead of pull data.

Regards.

Bill Ridgeway
Computer Solutions

 

[Serge]

Hello again Bill,

I'm trying to come up with a way to eliminate user input on sheet 2. That
sheet 2 will summerize the content of sheet 1 automatically. Someone told me
I would need to learn Visual Basic to achive this. Is that true?
Serge

 

[Bill Ridgeway]

I am sometimes sceptical when I hear the question "How do you do?" in terms
of function rather than objective. It suggests that the questioner is
asking about the bus stop for a particular service rather than checking if
the bus goes to his destination. A question in terms of how do I do this is
likely to elicit help only on that topic. A question in terms of how do I
get to the end point may just elicit a way of doing achieving what is
required in a way which the questioner may not have thought of.

OK. So now we know a bit about what you are trying to do we can try to help
you. In my experience of designing spread sheets it is sometimes best to
have an 'input sheet', sometimes a 'calculation sheet' and an 'output
sheet'. The 'input sheet' would be the only area in which users should be
changing data. The 'calculation sheet' would be the area in which the
inputs are combined to produce the output data. The 'output sheet' would be
an area which contains tables and graphs for printing. Obviously this is
only a design template to be adapted to needs.

I hope that something along these lines may be the answer to your query.

Regards.

Bill Ridgeway
Computer Solutions

 

[Serge]

can you provide a sample formula?

Hello Bill,
I could not have put it better myself in your explanation. I thought your
first paragraph had lot of humor to it, but very important to remember.

As you put it I have an 'input sheet' (sheet 1), a calculation sheet (which
is invisible) and an output sheet (sheet 2).

What is the stradigy to achieve what I want to accomplish?

Thanks

Serge

 

[Bill Ridgeway]

The best strategy is to have all sheets in a single workbook - as far as
possible.

You can, of course, have information 'pulled in' from another workbook by
reference to the cell. However, if the source cell is moved say, by a
change in the structure of the spreadsheet, the target is moved and you can
get some rather strange results. The main disadvantage is that everyone
either (sort of) trusts the computer or doesn't see a wayward figure and
bases a business critical decision on the outcome. This is especially so
when the incorrect data may be only marginally adrift. This can be overcome
by naming the target cell (<Insert><Name>) and referring to the name rather
than the cell. This will not work if the target workbook is moved or
deleted.

So, unless there is a very good business reason (or paranoia) it would be as
well to have all the spreadsheets together in a single workbook.

Hope that helps.

Regards.

Bill Ridgeway
Computer Solutions

 

[Bill Ridgeway]

Sheet 1- include labels to set out information to be included.

Sheet 2- this depends on the job in hand. When you think you've done this
put some dummy data in Sheet 1 and make sure the results are absolutely
accurate. You could also try breaking it by loading Sheet 1 with some silly
data. It's called idiot proofing but this is only as good as far as is
tested and idiots can do things you have not tested for. I like to colour
code cells containing formula as a reminder that the information is computed
not actual and that it shouldn't be touched. (This is OK if it isn't to be
printed). You could also hide the whole sheet (at least from someone who
doesn't know how to unhide it).

Sheet 3- again this depends on the job in hand.

Regards.

Bill Ridgeway
Computer Solutions

 

[Bill Ridgeway]

On re-reading my last post I've realised that I could have been a bit more
helpful.

Sheet 1 could also include some checks (formula) to point out that if data
is outside a predicted range that it may be an error AND to check with you.

Your next question is likely to be "How can all this be done". There's no
simple answer as it depends so much on the job in hand as to how simple /
complex it needs to be. One guiding rule. A simple spreadsheet is quick to
design and produce and takes up less space on the hard disk but is more
prone to error and, perhaps, has a less effective output. A more complex
spreadsheet takes a while to design and produce, takes up more hard disk
space, is less prone to error and has a more effective output. The reality
is whatever in between you want to make it.

Producing a spreadsheet is an 'art form' based on experience. I'm sure that
someone in product development could come up with a good description of the
process but it would look something like -

Take from the Client what he wants in terms of inputs and outputs.
Recommend options (including aspects which he doesn't know about).
Produce an outline.
A loop of produce a provisional product, refine the outline, test.
Produce a product.
Test the product in near real-life situation.
Release the product.

This process is true regardless of the size of the spreadsheet. It's the
time taken that differs.

If you would like help in producing the spreadsheet you may have to persuade
your company to pay for some consultation.

Regards.

Bill Ridgeway
Computer Solutions

 

[Serge]

Good Morning Bill,

It's 8 am in the Pacific North West.

Thank you so much for your comments.
Serge

 

[Bill Ridgeway]

One more thing (I must get around to writing all this down in one place).
Sorry to drag it out

Variables. It is a good thing to have all your variables in one place.
Collect all the basic facts such as 'mark up', 'sales tax' and the like in
one place (sheet 2), name the cells and refer to these variables by name. A
reference to these cells will always use the correct value. It saves
putting in a value many times - with the possibility of error. Also any
change of value (either factually or in a 'what if' situation) will be used
consistently and there is only one place to look if it is wrong.

Regards.

Bill Ridgeway
Computer Solutions

 

[Serge]

Hello Bill,
Thank you again for your input. All of my project is in one workbook. So far
it has worked well. But I needed to find a way to eliminate "user input" in
sheet 2 (output sheet).
I think I understand the information you provided me to this point.
Thanks
Serge