PDA

View Full Version : Reading Values from another sheet



Raabi
2011-05-19, 11:36 PM
Hello from Raabi

Would any geek help me in solving the following problem:

I have two Worksheets: "sheet1" and "sheet2".
In column "Value2" of the "sheet1", I need to put values from the corresponding row numbers and "event" column of the "sheet2".

I tried, in vain, a formula, in each cell of the column "Value2" of the "sheet1"; similar to the following Pseudo syntax:


=CELL("value",sheet2!$B$Row())

Please note that the two sheets are fairly large and frequently updated; and therefore just pasting the values from sheet2 is not a viable solution.

Raabi

Roger Govier
2011-05-20, 02:33 AM
Hi
You could try

=Index(Sheet2!$1:$65535,match(row(),sheet2!$A:$A,match("event",sheet2!$1:$1,0))

and copy down

Raabi
2011-05-20, 10:30 AM
Thank you so much, Roger, for your help.
Although Excel reports some error "#REF", I will try to work it out. However, further tip in this direction will be appreciated.

Regards

Roger Govier
2011-05-20, 11:17 AM
Hi

My apologies - typing in the early hours of the morning is obviously not very productive!!!
I made a complete hash of the formula.

The formula should have been

=INDEX(Sheet2!$1:$65535,MATCH(ROW(),Sheet2!$A:$A),MATCH("event",Sheet2!$1:$1,0))

but I guess you amended the formula OK, otherwise you wouldn't have got anything.

You would get #N/A errors if the number didn't exist in column A of sheet2, and a 0 if there was no value in the Event column of sheet2.

Can you attach a copy of your workbook - click Go Advanced and then use the paper clip to attach the file.