Cell Referencing

[Anonomous]

Hi all -
I have workbook with a few sheets, all of which are
referenced using cell referencing to the first sheet.
The only problem is - when I add something new or when I
sort it it doesn't update the other sheets properly - it
doesn't sort with the first sheet - which is what I do
want it to do.
Any suggestions as to what I can do to make these columns
completely the same as the first sheet? Maybe something
in VB?

 

[anonymous]

That actually works quite well. But now the problem is -
there are other columns that I want to stay corresponding
with their rows - they won't sort with their rows or
insert new rows.
Any idea how to fix that part of the problem?

Thanks for the help Mark!

-----Original Message-----
You could use this formula on the copy sheets:

=INDIRECT("SHEET1!"&ADDRESS(ROW(A1),COLUMN(A1)))

or if you want to suppress the zeros:

=IF(INDIRECT("SHEET1!"&ADDRESS(ROW(A1),COLUMN(A1))) ="","",INDIRECT("SHEET1!"&ADDRESS(ROW(A1),COLUMN(A1))))


Put this formula into cell A1 and copy it down and

across. Replace "SHEET1!" with your sheet name.

 

[Guest]

Standard cell References (ex =Sheet1!A1) should prevent the problem when you insert rows

Could you explain the sorting issue more. Are you saying that when you sort the first sheet, you want the data in this section on sheet 2 to remain the same? If so, isn't this what you where trying to prevent in your first post? Post a copy of your original formula

Regards
Mark Graesse
(e-mail address removed)

----- anonymous wrote: ----

That actually works quite well. But now the problem is -
there are other columns that I want to stay corresponding
with their rows - they won't sort with their rows or
insert new rows.
Any idea how to fix that part of the problem

Thanks for the help Mark

 

[Guest]

Basically I want the first 4 rows in each sheet to be the
same. But I want it so that when users add or delete it
still stays the same on the other sheets, and takes all
its row data with it.
I don't have a formula yet - I am still working on how to
do this.
But here is the code I am doing so that they can enter new
clients:

Sub cmdNewClient()


Dim strLastName As String
Dim strFirstName As String
Dim strFileNo As String
Dim strClerk As String

strLastName = InputBox("Enter Last Name", "New Client")
strFirstName = InputBox("Enter First Name", "New Client")
strFileNo = InputBox("Enter File Number", "New Client")
strClerk = InputBox("Enter Clerk's Name", "New Client")

Basically input boxes to get the strings, and then putting
them into the sheet - i just haven't figured out how to
sort so that it sorts all sheets.

-----Original Message-----
Standard cell References (ex =Sheet1!A1) should prevent

the problem when you insert rows.

Could you explain the sorting issue more. Are you saying

that when you sort the first sheet, you want the data in
this section on sheet 2 to remain the same? If so, isn't
this what you where trying to prevent in your first post?
Post a copy of your original formula.

 

[Guest]

I'm still having a little trouble understanding what you need. I understand that you want the first four rows of data to stay the same on all of your sheets after you sort the first sheet. Do the first four rows get sorted on the first sheet? What is special about these four rows? Will users be adding rows above or within these four rows. What do you need to happen if a user deletes one of these rows?

If you want to e-mail me the file I will take a look at it.

Regards,
Mark Graesser
(e-mail address removed)

----- (e-mail address removed) wrote: -----

Basically I want the first 4 rows in each sheet to be the
same. But I want it so that when users add or delete it
still stays the same on the other sheets, and takes all
its row data with it.
I don't have a formula yet - I am still working on how to
do this.
But here is the code I am doing so that they can enter new
clients:

Sub cmdNewClient()


Dim strLastName As String
Dim strFirstName As String
Dim strFileNo As String
Dim strClerk As String

strLastName = InputBox("Enter Last Name", "New Client")
strFirstName = InputBox("Enter First Name", "New Client")
strFileNo = InputBox("Enter File Number", "New Client")
strClerk = InputBox("Enter Clerk's Name", "New Client")

Basically input boxes to get the strings, and then putting
them into the sheet - i just haven't figured out how to
sort so that it sorts all sheets.

-----Original Message-----
Standard cell References (ex =Sheet1!A1) should prevent

the problem when you insert rows.that when you sort the first sheet, you want the data in
this section on sheet 2 to remain the same? If so, isn't
this what you where trying to prevent in your first post?
Post a copy of your original formula.