Text referencing method

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All
I used to copy grouped Items, their Specs and Qty from one sheet to another.
Sheet1
Items Spec Qty
T1 30 10
T2 50 10
D1 30 5
etc.

But when I introduce a new item T3 with Spec 100 and (new) Qty 10, and insert a row betwen T2 and D1, my Sheet2 looks like this:
Sheet2
Items Spec Qty(b/f)
T1 30 10
T2 50 10
T3 30 5 (instead of 100 and 10 respectively)
D1 (instead of 30 and 5 respectively)
etc.

How to copy data using text reference method instead of cell reference?
Thanks.
 
FangYR said:
Hi All
I used to copy grouped Items, their Specs and Qty from one sheet to another.
Sheet1
Items Spec Qty
T1 30 10
T2 50 10
D1 30 5
etc.

But when I introduce a new item T3 with Spec 100 and (new) Qty 10, and
Click to expand...
insert a row betwen T2 and D1, my Sheet2 looks like this:
 
Hi

not sure what's happening here ... what is the formula or otherwise in the
T1 spec cell in sheet2?
how are you inserting the row? and on what sheet as i can not duplicate your
results

also not sure what you mean by "grouped items" on sheet 1 ...

however, one idea - you might like to look at using a VLOOKUP function to
link the two sheets:
on sheet 2 for spec
=VLOOKUP(A2,Sheet1!$A$2:$C$10,2,false)
and on sheet 2 for qty
=VLOOKUP(A2,Sheet1!$A$2:$C$10,3,false)

Hope this helps
Cheers
JulieD


FangYR said:
Hi All
I used to copy grouped Items, their Specs and Qty from one sheet to another.
Sheet1
Items Spec Qty
T1 30 10
T2 50 10
D1 30 5
etc.

But when I introduce a new item T3 with Spec 100 and (new) Qty 10, and
Click to expand...
insert a row betwen T2 and D1, my Sheet2 looks like this:
 
Hi
if column A in sheet one is entered manually you can use
VLOOKUP for this. e.g.
=VLOOKUP(A1,'sheet2'!$A$1:$C$100,2,0)
-----Original Message-----
Hi All
I used to copy grouped Items, their Specs and Qty from one sheet to another.
Sheet1
Items Spec Qty
T1 30 10
T2 50 10
D1 30 5
etc.

But when I introduce a new item T3 with Spec 100 and
Click to expand...
(new) Qty 10, and insert a row betwen T2 and D1, my Sheet2
looks like this:
 
One guess is you might have
copy > pasted "link" formulas in Sheet2 such as:

In A1: =Sheet1!A1
In A2: =Sheet1!A2
etc

Try in a new Sheet3
-----------------------
Put in A1:

=OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)

Copy A1 across to C1, then copy down
a "safe" max number of rows which can cover the likely
max increase in rows for new items in the source Sheet1

For a neater look, extraneous zeros can
be suppressed from showing in Sheet3 via:
Tools > Options > View tab > Uncheck "Zero values" > OK

When new items are added in-between existing items to the source table in
Sheet1 via insertion of new rows*,
these new items will be updated in Sheet3
but not in Sheet2 (albeit Sheet2's links will update)

*Presume you're using Insert > Rows to do this,
rather than Insert > Cells
(which might cause errors if you omit changing
the default "Shift cells down" in the Insert dialog to "Entire row")

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
FangYR said:
Hi All
I used to copy grouped Items, their Specs and Qty from one sheet to another.
Sheet1
Items Spec Qty
T1 30 10
T2 50 10
D1 30 5
etc.

But when I introduce a new item T3 with Spec 100 and (new) Qty 10, and
Click to expand...
insert a row betwen T2 and D1, my Sheet2 looks like this:
 
Back
Top