How to Update the Worksheets field value based on updates done in Main Worksheet.

**arunsinghpundir** · 10-08-2007, 10:50 AM

Hello Excel Masters -
How to Update the Worksheets field value based on updates done in Main Worksheet.
As i have two different worksheets (Which can be even more then 5) and i want to update the Column Field value of "State" in work sheet "B" & "C" based on the updates done in "State" Column Field value in Worksheet A.
I just want to update the "State" Column Field value in Worksheet "A" and it should update the "State" Column Field value in Worksheet "B" & "C". Only the criteria to match is Number. Based on number i want to search the values in Worksheet "B" & "C" and want that these values also get updated.

Please Help. Attached is the same data.
All help is appreciated in advance.

**NBVC** · 10-08-2007, 12:45 PM

To me it looks like you are asking for a Vlookup formula...

In C2 of sheets B and C, enter formula: =VLOOKUP(A2,'Sheet A'!$A$2:$C$7,3,0)

and copy these formulas down in each sheet

**arunsinghpundir** · 10-09-2007, 05:14 AM

Hello NBVC.
Thanks for a prompt reply-
Well VLOOKUP is surely something for which i am lookign for but here i have some different requirement.

In Worksheet "B"/"C" etc .....first I want to search a value in Column "A" of worksheet "A" and if this value existing in worksheet "A" udner Column "A" then only i want to make any updates.

Here i need to perform the search because in worksheet "A" i don't have the sorted order of the values of column "A".And in column "A" of worksheet "A" there are soem thousand values which consist of values from worksheet "B"/"C" etc etc.

When i search the value by entering the Number (which i want to search in place of A2) and Number in worksheet "A" is alphanumeric then this formula is not working.
Please seee the attached sample data.

Please help -

**NBVC** · 10-09-2007, 07:46 AM

With alphanumeric or alpha search criteria, you need to enclose the search phrase in double-quotes.

e.g =VLOOKUP("SPECM10001",'Sheet A'!$A$2:$C$8,3,0)

Try that formula, copied down.

**arunsinghpundir** · 10-09-2007, 09:00 AM

Hello NBVC

Thanks again ...but still getting some problem with the atatched XLS Sheet....
It is displaying the error " A Value is not available to the function or formula".

Please do let me know why i am getting this error as well ......
Please have a look at this!

Thanks again for helping me out.

**NBVC** · 10-09-2007, 09:08 AM

The #N/A formula was due to the fact the match item in Sheet A had an extra space at the end... the matches have to be exact so delete that space....

Also, instead of entering the string into the formula, why not just reference the item in column A of sheets B and C....

e.g. instead of =VLOOKUP("SEPCM00024823",'Sheet A'!$A$2:$C$8,3,0) try using =VLOOKUP(A2,'Sheet A'!$A$2:$C$8,3,0) copied down.