ExcelBanter - Basic Excel for Macro Experts - replicating data across multiple cells

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Basic Excel for Macro Experts - replicating data across multiple cells (https://www.excelbanter.com/excel-discussion-misc-queries/94023-basic-excel-macro-experts-replicating-data-across-multiple-cells.html)

Basic Excel for Macro Experts - replicating data across multiple cells

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.

Basic Excel for Macro Experts - replicating data across multiple cells

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

--
swatsp0p

------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=552002

Basic Excel for Macro Experts - replicating data across multiple cells

"swatsp0p" wrote in
message

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.

Basic Excel for Macro Experts - replicating data across multiple cells

"swatsp0p" wrote in
message 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.

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

Basic Excel for Macro Experts - replicating data across multiple cells

"David Smithz" wrote in message
k...

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.

Basic Excel for Macro Experts - replicating data across multiple cells

Basic Excel for Macro Experts - replicating data across multiplecells

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.

Don Guillett wrote:

Only if spaces in the sheet name

my sheet name yes
mysheetname no

--
Don Guillett
SalesAid Software

"David Smithz" wrote in message
. uk...

"David Smithz" wrote in message
k...

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.

--

Dave Peterson