Recommended solution

[Steve M]

In order to retain the original data in a worksheet, I have created a
second worksheet that pulls cells row by row, cell by cell to do data
manipulation and calcuate results. My problem is when a new row is
added to the original worksheet it does not update the second
worksheet with the new data. If I re-sort the original sheet the new
row does come over. To get the data to the second worksheet I'm
basically just referencing the original worksheet cells from the
second, eg, ='sheet1'!A1 across all columns and all row. Is there a
better way to bring the original data over that will allow for
automatically updating on the second worksheet? I must keep the
second worksheet protected, except for 4 columns for user input. Any
help is appreciated.

 

[pub]

In order to retain the original data in a worksheet, I have created a
second worksheet that pulls cells row by row, cell by cell to do data
manipulation and calcuate results. My problem is when a new row is
added to the original worksheet it does not update the second
worksheet with the new data. If I re-sort the original sheet the new
row does come over. To get the data to the second worksheet I'm
basically just referencing the original worksheet cells from the
second, eg, ='sheet1'!A1 across all columns and all row. Is there a
better way to bring the original data over that will allow for
automatically updating on the second worksheet? I must keep the
second worksheet protected, except for 4 columns for user input. Any
help is appreciated.

if you want an exact copy, you might want to look into the indirect()
formula. you can google or F1 the explanation details
but for now, copy&paste this into your second worksheet

=INDIRECT("sheet1!"&COLUMN()&":"&ROW())

then copy&paste down and across
if you insert rows or columns, then you are going to get a bunch of
zero's...so you might want to wrap an if() statement to look for "".

enjoy.

 

[Steve M]

I'm not really trying to retain an exact copy, just cols A&B, and Z-AG
of the first worksheet. A&B are the food commodity item and packaging
description, Z-AG is the prices/brand from 4 vendors. I tried the
indirect function which you suggested but it only worked in the first
2 cols. The other cols were showing 0.00.

 

[Steve M]

Is there a way from the second worksheet to reference the row the data
is in the first worksheet by using a combination of the INDIRECT and
another function? IOW, using INDIRECT I'm getting cols A&B from
worksheet A, but because my cols in the second worksheet are not the
same as the first worksheet, the calcualations are out of sync once a
new row has been inserted in the first worksheet. Is there a way to
direct the formula's in the second worksheet to pull data from the
first worksheet using the row number that was returned using INDIRECT
on cols A&B on the second? I hope this makes sense. Thanks for your
input.

 

[pub]

I'm not really trying to retain an exact copy, just cols A&B, and Z-AG
of the first worksheet. A&B are the food commodity item and packaging
description, Z-AG is the prices/brand from 4 vendors. I tried the
indirect function which you suggested but it only worked in the first
2 cols. The other cols were showing 0.00.

oh, sorry i think i mis-understood
indirect() allows you to hardcode text strings into the formula

=INDIRECT("Sheet1!AG"&ROW())

so this will pull column AG and the row
you can change the AG to any column you want to hardcode

 

[pub]

Is there a way from the second worksheet to reference the row the data
is in the first worksheet by using a combination of the INDIRECT and
another function? IOW, using INDIRECT I'm getting cols A&B from
worksheet A, but because my cols in the second worksheet are not the
same as the first worksheet, the calcualations are out of sync once a
new row has been inserted in the first worksheet. Is there a way to
direct the formula's in the second worksheet to pull data from the
first worksheet using the row number that was returned using INDIRECT
on cols A&B on the second? I hope this makes sense. Thanks for your
input.

i guess i should have read this one 1st.
not sure i understand anymore. you might want to re-ask the question as
new. make sure you note what weve tried..someone might have a better
answer. i would also suggest giving some sample data to show whats
happening, and what you want the result to be.

you want to add a row in sheet1 and you want your rows to be in sync?
its just your columns that are not the same?
the easiest thing to do
- select sheet1
- ctrl-click on sheet2
if you hit control and select sheet 2, then both sheets will be
highlighted.
- then insert a row
- now the row will be inserted on both sheets
- then deselect the sheets (by clicking on the sheet that you are not
currently on), so that you are only working on 1 sheet at a time.