Basic Excel for Macro Experts - replicating data across multiple cells

[David Smithz]

Hi there,

I have to have a crash course in setting up an Excel spreadsheet, so that
when data is entered on one worksheet it is duplicated across many other
worksheets in the same spreadsheet.

Under pressure I was asked how quickly I could do this, and have been told
to give an example very quickly.

I therefore wanted to ask, for someone with vb6 programming experience (but
no office macro experience) is this something easily achievable. Therefore
entering data into one worksheet, which will after entering populate the
data in many other work sheets.

Thank you for any input on this matter (any guidance to make it easy
welcome).

Dave.

 

[Don Guillett]

Since you were honest about homework
just hold down the ctrl key>select the desired sheets>enter someting on the
1st and it will be on all.
for all sheets>right click sheet tab>all sheets

 

[swatsp0p]

You don't give us a lot of information to go on. Such as <where> on the
other sheets you want the information. How many cells on the original
sheet need to be replicated, etc.

If it goes in the same cell on each sheet and there are only a few
cells to replicate, simply select the desired sheets (hold down CTRL
while clicking on desired sheet tabs) then enter a formula in the
desired cell(s) such as:

=Sheet1!A1 etc.

Now what ever is entered on sheet 1 in cell A1 will appear in the each
of the sheets in the cells with the formula.

Good Luck

Bruce

 

[David Smithz]

You don't give us a lot of information to go on. Such as <where> on the
other sheets you want the information. How many cells on the original
sheet need to be replicated, etc.

If it goes in the same cell on each sheet and there are only a few
cells to replicate, simply select the desired sheets (hold down CTRL
while clicking on desired sheet tabs) then enter a formula in the
desired cell(s) such as:

=Sheet1!A1 etc.

Now what ever is entered on sheet 1 in cell A1 will appear in the each
of the sheets in the cells with the formula.

OK thanks for the input. What I did mean is that on one quite complicated
worksheet, the value entered is to be duplicated across other worksheets but
in different cell positions, sometimes multiple.

E.g. Maybe the first worksheet is Invoice and another worksheet is invoice
breakdown.
Where the invoice number is entered on the first worksheet, this is
replicated for each part number for the invoice related to that number.

OK probably not the best example actually, but I'm trying to demonstrate the
fact that the field on the first worksheet can be copied to many different
multiple cell locations on other worksheets and automatically updated if
there is a change.

But from the information you have given my I have been able to work it out.
All I do is simply enter the sheet number and cell number as formula of
where I want the information to come from. (e.g. =SheetInvoice!C1) Easy! At
least it seems easy so far.

Any more hints or tips that will help me with this then please let me know.

Cheers

David.

 

[David Smithz]

cells to replicate, simply select the desired sheets (hold down CTRL
while clicking on desired sheet tabs) then enter a formula in the
desired cell(s) such as:

=Sheet1!A1 etc.

Ahh, I do have a further question regarding this. What if Sheet1 is called -
Example Sheet name with space
-
I am looking it up, but if you happen to know off hand how I reference this
worksheet then great. I have tried quotes and brackets with no luck.

anyone know?

Cheers

 

[David Smithz]

Ahh, I do have a further question regarding this. What if Sheet1 is
called -
Example Sheet name with space
-
I am looking it up, but if you happen to know off hand how I reference
this
worksheet then great. I have tried quotes and brackets with no luck.

anyone know?

Ok found it in the end. I needed to use single quotations.

Thanks for input.

 

[Don Guillett]

Only if spaces in the sheet name

my sheet name yes
mysheetname no

 

[Dave Peterson]

Not just only when there are spaces.

If the worksheet name is a number, you'll need them, too.
='1234'!a1

To the OP:
If you're building a formula, if you add them and excel doesn't need them, excel
will get rid of them.