Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.setup,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Make Columns A&B on Sheet2 always equal Sheet1 table columns A&B

On Worksheet 1 named Transfer settings I have a table called
"transfer_settings". Column A is "Transfer Name" and Column B is "Item #"

I would like Worksheet 2 Called "Event Fullfillment" to have Columns A & B
always equal the columns A&B in the table on worksheet 1.

Is there a function for this
--
Thanks
KnightBall
  #2   Report Post  
Posted to microsoft.public.excel.setup,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,071
Default Make Columns A&B on Sheet2 always equal Sheet1 table columns A&B

Say your data in worksheet 1 starts in A2. In worksheet 2, cell A2, enter
an equal sign. Then select worksheet 1 and click on cell A2. Hit Enter.
In worksheet 2, select A2 and drag that formula down with the fill handle as
far as you need to. Repeat all this for column B. There are other ways if,
for some reason, you don't want the above. HTH Otto
"KnightBall" wrote in message
...
On Worksheet 1 named Transfer settings I have a table called
"transfer_settings". Column A is "Transfer Name" and Column B is "Item #"

I would like Worksheet 2 Called "Event Fullfillment" to have Columns A & B
always equal the columns A&B in the table on worksheet 1.

Is there a function for this
--
Thanks
KnightBall



  #3   Report Post  
Posted to microsoft.public.excel.setup,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default Make Columns A&B on Sheet2 always equal Sheet1 table columns A&B

On Sun, 16 Aug 2009 09:23:01 -0700, KnightBall
wrote:

On Worksheet 1 named Transfer settings I have a table called
"transfer_settings". Column A is "Transfer Name" and Column B is "Item #"

I would like Worksheet 2 Called "Event Fullfillment" to have Columns A & B
always equal the columns A&B in the table on worksheet 1.

Is there a function for this


If the A & B columns have a limited number of rows, and particularly a
known number of rows, then you can use VLOOKUP for it. You can also make
direct calls like in

=(sheet1!A1) in the sheet 2 A1 position.

You can also declare a known "table" (cell array) of data and give it a
name, which leaves you with a "named range" in excel, then you can refer
to that range easier than referring to actual cell locations.
  #4   Report Post  
Posted to microsoft.public.excel.setup,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default Make Columns A&B on Sheet2 always equal Sheet1 table columns A&B

"Otto Moehrbach" wrote...
Say your data in worksheet 1 starts in A2. *In worksheet 2, cell A2, enter
an equal sign. *Then select worksheet 1 and click on cell A2. *Hit Enter.
In worksheet 2, select A2 and drag that formula down with the fill handle as
far as you need to. *Repeat all this for column B. *There are other ways if,
for some reason, you don't want the above.

....

Picky: inserting or deleting rows in either worksheet could alter
this. Row DELETION could be handled by making the Sheet2 formulas

Sheet2!A2: =INDEX(Sheet1!$A:$A,ROW())

Sheet2!B2: =INDEX(Sheet1!$B:$B,ROW())

And if column deletion could also be an issue,

Sheet2!A2: =INDEX(Sheet1!$1:$65536,ROW(),COLUMN())

would always refer to the corresponding cell in Sheet1.

Only event handlers could deal with row/column INSERTION in which case
new formulas for newly blank cells would be needed.
  #5   Report Post  
Posted to microsoft.public.excel.setup,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default Make Columns A&B on Sheet2 always equal Sheet1 table columns A

I have a question about the formula. I just set up sheet 2 to match th
ings entered in sheet 1 but my formula is =Sheet1!A2 not =Sheet2!A2

"Harlan Grove" wrote:

"Otto Moehrbach" wrote...
Say your data in worksheet 1 starts in A2. In worksheet 2, cell A2, enter
an equal sign. Then select worksheet 1 and click on cell A2. Hit Enter.
In worksheet 2, select A2 and drag that formula down with the fill handle as
far as you need to. Repeat all this for column B. There are other ways if,
for some reason, you don't want the above.

....

Picky: inserting or deleting rows in either worksheet could alter
this. Row DELETION could be handled by making the Sheet2 formulas

Sheet2!A2: =INDEX(Sheet1!$A:$A,ROW())

Sheet2!B2: =INDEX(Sheet1!$B:$B,ROW())

And if column deletion could also be an issue,

Sheet2!A2: =INDEX(Sheet1!$1:$65536,ROW(),COLUMN())

would always refer to the corresponding cell in Sheet1.

Only event handlers could deal with row/column INSERTION in which case
new formulas for newly blank cells would be needed.



  #6   Report Post  
Posted to microsoft.public.excel.setup,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default Make Columns A&B on Sheet2 always equal Sheet1 table columns A

On Mon, 17 Aug 2009 16:54:01 -0700, joemomma
wrote:

I have a question about the formula. I just set up sheet 2 to match th
ings entered in sheet 1 but my formula is =Sheet1!A2 not =Sheet2!A2


He was showing you which cell to put the formula in. The formula
itself STARTS at HIS equal sign.

LOOK again.

I'll strip his 'pointers' away.

=INDEX(Sheet1!$A:$A,ROW())

=INDEX(Sheet1!$B:$B,ROW())

And if column deletion could also be an issue,

=INDEX(Sheet1!$1:$65536,ROW(),COLUMN())


Now, the reference you claimed was wrong is merely the location where
he wants you to place the above strings.
  #7   Report Post  
Posted to microsoft.public.excel.setup,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default Make Columns A&B on Sheet2 always equal Sheet1 table columns A

Thank you my bad! been practicing on 2 little sheets sheet 1 (master1),
sheet 2 (fill1). inserting the formula per the instuctions works fine. sheet
1 is the main sheet where data is added or changed. When I insert a row in
sheet 1 the formulas on sheet 2 are intact, but it doesn't insert a row in
sheet 2. Joe

"OutsideObserver" wrote:

On Mon, 17 Aug 2009 16:54:01 -0700, joemomma
wrote:

I have a question about the formula. I just set up sheet 2 to match th
ings entered in sheet 1 but my formula is =Sheet1!A2 not =Sheet2!A2


He was showing you which cell to put the formula in. The formula
itself STARTS at HIS equal sign.

LOOK again.

I'll strip his 'pointers' away.

=INDEX(Sheet1!$A:$A,ROW())

=INDEX(Sheet1!$B:$B,ROW())

And if column deletion could also be an issue,

=INDEX(Sheet1!$1:$65536,ROW(),COLUMN())


Now, the reference you claimed was wrong is merely the location where
he wants you to place the above strings.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how can I make a cell content equal the tab name 'sheet1' ? balder Excel Worksheet Functions 13 April 2nd 23 07:22 PM
copy data from sheet2 to sheet1 when sheet2 has variable # of rows Anne Excel Discussion (Misc queries) 6 February 27th 09 10:48 PM
[=Sheet1!A1] - if i use Column A1 ('Sheet1') / A2 ('Sheet2') Lawrence C H Tan Excel Worksheet Functions 0 January 19th 07 09:29 PM
Combine 3 Columns on Sheet2 to Display in 1 Column on Sheet1 Summer Excel Worksheet Functions 12 June 14th 05 02:56 PM
can i type sheet1 A5 and make it show Sheet2 A6 cell HelpMe Excel Discussion (Misc queries) 1 February 25th 05 02:44 AM


All times are GMT +1. The time now is 09:41 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"