Formula help on information sharing between 3 worksheets

[Guest]

I have 1 of 3 worksheets that I would like to have the cells transfer to
specific cells in the other 2 worksheets in very different manners. The
worksheets are much larger then these, but this is the general idea. Each
worksheet has multiple named sections. Which cells that have information and
which will be blank will change every week in worksheet 1.

Worksheet 1

AB 1 2 3 4
a xyz
b rst
c
d
AC 1 2 3 4
f
g
h rst
i xyz

Worksheet 2

AD 1 2 3 4
a AB
b
c
d AC
AE 1 2 3 4
f
g AB
h AC
i

Worksheet 3

AF 1 2 3 4
a xyz
b rst
c
d
AG 1 2 3 4
f rst
g xyz
h
i

Part 1:
I only enter information in Sheet 1. I would like Sheet 2 to scan Sheet 1
and whenever a cell has it a certain word "xyz†I need it to transfer the
section name to the relative position Sheet 2 , Section AD. If it should have
a different word "rst" it would transfer the section name to Sheet 2, Section
AE. An error message if that cell already has information in it would be a
nice bonus.

Part 2:
Worksheet 3 is just a list of all filled cells in each section of worksheet
1 with no relation to the grid. This is an "exact word" transfer between
sections. Section AB to Section AF and Section AC to Section AG, but just in
a list form top to bottom, right to left.

 

[mrice]

I would suggest that you set up a macro linked to th
worksheet_change_event on sheet1 which looks for the trigger words an
copies as appropriate to your two other sheets

 

[Guest]

I dont understand. I am not very proficient with many of the functions in
excel.

 

[mrice]

I hope that the attached solves the first of the problems - you can see
the code by hitting ALT F11 and going to the sheet1 tab.

I don't fully understand the sheet 3 format, what happens if the data
is in something other than the first column.


+-------------------------------------------------------------------+
|Filename: Book2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4753 |
+-------------------------------------------------------------------+

 

[Guest]

When I click the link the message on the site says "Invalid Attachment
specified"

Only column 1 on sheet 3 is relevant here, the other columns have
independent data in them. Its just a listing of everything that is typed in
Sheet 1.
Imagine sheet 1 as a day of the week calendar ,across, and time of day,
down. You may have 2 entries for monday in row 3 and 7 , 3 for tuesday in row
1, 4, and 10 and so on...sheet 3, column 1 will just be a list of all the
entries with no spaces for cells not filled in sheet 1

 

[mrice]

Strange, it worked for me.

Can you drop me an e-mail via my home page and I'll send what I hav
via e-mail

 

[Guest]

I got the link to work.
So far it looks like it works perfectly. Thank you very much, this has been
bugging me for a few months now. I thought it was possible, but as I said
earlier, I'm not very proficient in many of excels functions.

 

[mrice]

No problem - glad to help.

The attachment has a refinement to cope with sheet 3.


+-------------------------------------------------------------------+
|Filename: Book2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4754 |
+-------------------------------------------------------------------+