auto-transfer data

Thanks a lot JB,
It works. Could you be kind to please explain this string, so that I can understand its limitations and expanded applicability ?

In particular, it seems that the MATCH is finding out data corresponding to only $E5 and not $E6. Is it correct ?

Thanks again, I'll read about the INDEX and MATCH functions to educate myself and then will ask you today itself if some more of your excellent help is required.

Thanks a lot JB. Your input has been very helpful.
Best regards.

Hi JB,
Excuse me for bothering you again.

Could you please help me with its slight variant also, wherein WE HAVE TO DO IT WITHIN THE SAME FILE, BUTT DIFFERENT SHEETS. So, the problem becomes :
There’s one “master SHEET”. Two columns in master SHEET are titled “Name-1” and “Name-2”, and all columns in front of it carry “Properties” corresponding to these names.
In a “second” SHEET, when we choose/type the Name-1 and Name-2, corresponding properties should automatically be picked up from the master SHEET.

Thanks a lot in anticipation.

Hi Rajat,

I am new to Excel Forum. But, I thought I could help.

Consider the name of the first sheet to be 'Master Sheet'.

Now, in the second sheet, you can put either of the two formulas in G5 and you can copy it across the entire range:

Option 1 ---> =INDEX('Master Sheet'!D:D,MATCH($E5,'Master Sheet'!$B:$B,0))

Option 2 ---> =VLOOKUP($E5,'Master Sheet'!$B$5:$V$20,COLUMN(F$1)-3,0)


The first option is a minor modification of Mr. JB's solution.

Hope you find the information useful .

--Karan--

Thanks Karan, it works. thanks.

Karan , JB,
Would it be possible to modify the formula in G5 such that the MATCH() formula looks for the value in E5 aswell as F5 (in the column B and C on the other sheet) before returning the answer as row number for INDEX to use it ?

Hi Rajat,

I have written a macro for you. It is in the attached file.

By simply entering the values in the 'Name-1' and 'Name-2' columns in the second table, you will automatically get the values in the adjoining columns if a match is found in the 'Master Sheet'.

Make sure you enable macros when you open the file.

Also, be sure that the Properties columns in both the sheets are in the same order.

The macro is written in the Worksheet_change module of the sheet 'Second Sheet'. If you want the macro to run on your file, just copy it from here and paste it in the 'Second Sheet' code of your file. You can even customize it by modifying the range and other things. If you have any more doubts, do write back .

--Karan--

Many thanks again Karan and JB.
Your help has been truely fantastic, on the spot, and most appreciated.

Makes me wonder how do these intricate aspects of Excel come so easily to you ? Is it some formal training, plus immense personal interest, or both ?

I am an engineer and see normal fellow engineers being totally ignorant of the strengths of excel. Our world would be much easier if we were as familiar in excel.

So, the next questions are :
1. can I take this formal education in Excel somewhere in India ?
2. Is there a knowledgeable guy like yourselves, who comes over and educate these excel intracacies to our team in our premisis in Delhi ?
3. Could I have your direct email ID ?

Hi Rajat,

I have sent you me email id in a personal message.

--Karan--