PDA

View Full Version : Set a value from different sheet in other sheet



MPDK166
03-31-2011, 08:06 AM
I have got a value in sheet1 cell B2 (a date)
if this value is equal to a cell in sheet2 column("A") then
I want the value of Sheet2 cell B +rownumberm of column(A) set in Sheet1 column ("C2")

Can this be done with an excel function or do I need to use VBA and if yes, how to...?

nepotist
03-31-2011, 09:25 AM
Please find the attached sheet:

The formula in cell "C2" should be
OFFSET(INDIRECT("Sheet2!"&ADDRESS(MATCH(B2,Sheet2!A:A,0),COLUMN(Sheet2!A:A),4)),0,1)+MATCH(B2,Sheet2! A:A,0)

If what I have attached is not what you want, would you please upload the sample to understand it better

MPDK166
03-31-2011, 09:46 AM
Nepotist,

It is not working (also your book), probably because i have got a dutch excel version :S

Maybe it is easier to implement it via VBA, because that is in english...

Do you know the VBA code (btw: your values in the test file is exactly what I mean :D)

nepotist
03-31-2011, 10:09 AM
Here is a website that list translation in to other languages, see if you make it work in your version. When you can make it work with inbuilt functions why go for VBA?

http://wwwhome.ewi.utwente.nl/~trieschn/excel/excel.html

MPDK166
03-31-2011, 11:02 AM
I tested the same workbook on a 2007 version and it does work, however, I need to use it on 2003 version. So the error is in the compatibality... Any ideas?

nepotist
03-31-2011, 11:12 AM
What is the error? did you convert the formulas to Dutch?

MPDK166
03-31-2011, 11:51 AM
Error: N/B;
It is in the match part, I think.

Translation works, since the 2007 version is also dutch!

nepotist
03-31-2011, 11:57 AM
You mean N/A ? It would make sense to throw that. You need to use IF THEN statement And ISERROR to check what you need to do if there is match and what to do if there isn't a match

=If(Iserror(OFFSET(INDIRECT("Sheet2!"&ADDRESS(MATCH(B2,Sheet2!A:A,0),COLUMN(Sheet2!A:A),4)),0,1)+MATCH(B2,Sheet2! A:A,0) )),"", OFFSET(INDIRECT("Sheet2!"&ADDRESS(MATCH(B2,Sheet2!A:A,0),COLUMN(Sheet2!A:A),4)),0,1)+MATCH(B2,Sheet2! A:A,0) )

The above modified formula would give you a Cell (Bx) value plus row number value is there is a match , else a blank cell if there sin't

MPDK166
03-31-2011, 12:31 PM
unstead of earlier sugestions... can we try it in VBA? I have got also some other scripts in this file. Therefore i personally prefer vba.