Excel 2007 : Dynamic references to cells

[komyg]

Hello I have two spread sheets with that have two columns of numbers that are supposed to reference each other for example:

Spreadsheet 1 column B:
10320001
10320004
10320005
10320006
10320007

Spreadsheet 2 column F
10320001
10320004
10320005
10320005
10320006
10320007
10320007

Now I need to change the values of the spreadsheet 1 and keep them consistent with spreadsheet 2, for example if I change 10320007 to 5689 in spreadsheet 1, then all the values in spreadsheet 2 that are 10320007 must change to 5689.

I have been able to find a formula that returns to me what is the line of the spreadsheet 1 that corresponds to a value in spreadsheet 2 (this is a standard excel formula, but I only know it's name in portuguese: CORRESP). In the example above the function would return 5 if I inputted 10320007.

Using this I could create a new column C in spreadsheet 1 with the new numbers that I want to update.

10320001 5685
10320004 5686
10320005 5687
10320006 5688
10320007 5689

Then I would get value of the cell defined by the intersection of the line number I got from the function and column C, that would amount to something like this: ='Spreadsheet1'!C (myFunction(10320007)).

So far excel has returned me nothing but errors...

Could you help me?

Thanks,
Komyg

[ratcat]

G'day komyg,

and welcome to the Exceltip forum.

I'm sorry I am having trouble understanding what you would like to accomplish.

Firstly the function named 'CORRESP' translates into 'MATCH'

Do you want the same cell that has the number 10320007 to change into the number 5689 ?

Or to help you with the formula you supplied. ='Spreadsheet1'!C (myFunction(10320007)).

Try this

=ÍNDICE('Spreadsheet1'!C:C,CORRESP(10320007,'Spreadsheet1'!B:B,0))

English version

=INDEX('Spreadsheet1'!C:C,MATCH(10320007,'Spreadsheet1'!B:B,0))

Please note: That you have to replace the , in the formula with ; and where the number is you can replace that with a cell reference (if you like).

Here is a link that will help you translate the english formulas you see here in this great forum

Translation Page

Hope this helps