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.

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

Hello NBVC

Thanks again ...If i use just reference the item in column A of sheets B and C and not enter the string into the formula then in worksheet "B" it is taking the refrence of "A2" column of worksheet "B" only and not of the worksheet "A".
Similarly in worksheet "C" it is taking the refrence of "A2" column of worksheet "C" only and not of the worksheet "A".
but if i enter the string into the formula then then it seems to be working ...
kindly let me know whats the problem .......as i myself don't wana use string into the formula...kindly help!

Thanks again for helping me out.

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

You should only enter the formula in the first row (C2) and then copy down the formula, this will automatically change the reference to column A to coincide with the next entry in A.

To copy the formula down, click and hold the little black square at the bottom right corner of the active cell and drag it down the column.

See attached.

**arunsinghpundir** · 10-10-2007, 03:06 AM

Hello NBVC -

Perfect ...thats what i was looking for and it helped .But one more issue related to the same, as in Worksheet "B" there are some entries in column "A" which are not present in column "A" of Worksheet "A" ,then i want to keep the value of column "C" in Worksheet "B" intact.

Currently if i use =VLOOKUP(A2,'Sheet A'!$A$2:$C$8,3,0) in worksheet "B" under column "C" then for unmatached records it is displaying the value as "#NA".
I want to search the value of Column "A" of worksheet "B" in Column "A" of worksheet "A" and if a matach is found then update the value of Column "C" of worksheet "B" otherwise keep the value of Column "C" of worksheet "B" as what is mentioned.
Please help.
Thanks again for helping me out.

**NBVC** · 10-10-2007, 07:44 AM

Unfortunately, Excel does not allow you to choose a formula result or leave the cell "untouched" (unless you get into programming it with VBA).

What I can suggest is that you include another column with a new formula that will look for a match first and if it is not found, then it will take from the original State column.....

See sample attached...formula in D2 copied down is:

Please Login or Register  to view this content.

I hope this works for you

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

LinkBack

Thread Tools

Rate This Thread

Display

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions