Make Columns A&B on Sheet2 always equal Sheet1 table columns A&B


K

KnightBall

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
 
Ad

Advertisements

O

Otto Moehrbach

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
 
O

OutsideObserver

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.
 
H

Harlan Grove

Otto Moehrbach said:
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.
 
J

joemomma

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
 
O

OutsideObserver

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())
=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.
 
Ad

Advertisements

J

joemomma

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top