Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Referencing

0

Hello there,

I have a little bit complex case here, I'm looking for a formula that will display the data in cell E3:E6 with referencing column C&D from sheet 2 and every time I update column C it will display it in column E based on the second sheet data.

I was thinking about lookup function but didn't know how to do that.

attached is an example please, know if you need any info,

exceldata-validation
MS Office Version: Jana
posted by: Jana86 (rep: 6) on Tue Dec 4, 2018 at 11:47 am
Answer
Discuss

Answers

0
Selected Answer

I understand your task as follows.

"Find the Size in Sheet2 which matches the first description in column C in Sheet2.Column(Ax)
then concatenate that datum with
the datum found at the intersection of the second description in column C in Sheet2.Column(Bx) and the column specified in column D."

Based on this understanding the desired result in you have in E4 should be wrong, but perhaps I don't understand correctly. I am taken aback by your sample data in column C ("A1,B1") which always specify the same row in Sheet2.

Anyway, to produce a solution to fit my understanding I created 3 named ranges on Sheet2 as follows.

  1. DataA  =Sheet2!$A$2:$B$5
  2. DataB =Sheet2!$C$2:$F$5
  3. Zones =Sheet2!$D$1:$F$1

The formula below refers to thesed ranges. You can paste it in E3 of your trial worksheet.

=VLOOKUP(TRIM(LEFT($C3,FIND(",",$C3)-1)),DataA,2,TRUE)&"_"&VLOOKUP(TRIM(MID($C3,FIND(",",$C3)+1,50)),DataB,MATCH($D3,Zones,0)+1,TRUE)

The structure of this formula is explained in the attached workbook's Sheet1, columns F:S. Note that the TRIM function is added prophylacticly because I don't know how the data in your column C are entered. If it is done by humans TRIM is a good idea. If it is done by computer it might be superfluous.

posted by: Variatus (rep: 4889) on Tue Dec 4, 2018 at 8:30 pm
Discuss

Discussion

I'm sorry for the bad short explanation it was so confusing but you got this as well I appreciate that! The only issue is that there are no limits for the data I tried to add if statement but it didn't work for any of these groups 
DataA  =Sheet2!$A$2:$B$5 DataB =Sheet2!$C$2:$F$5 Zones =Sheet2!$D$1:$F$1 Would you help me with that?
For example, if this data in the range of these cells then apply it if not apply ***

Thank you so much.
Jana86 (rep: 6) Dec 6, '18 at 8:15 am
First, of course I shall help you. Second, I'm afraid I dont fully understand your problem, especially the last sentence. Third, this isn't the place to discuss it. Ask another question.
In the formulas, you can always replace the name of a named range with the address. Instead of DataA you can write Sheet2!$A$2:$B$5 and modify that address in whatever way. However, you can also modify the address of a named range in the same ways, and that has the advantage of doing it in only one place instead of changing multiple formulas. If your problem is about making a named range dynamic, please ask a question with that title.
Variatus (rep: 4889) Dec 6, '18 at 7:59 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login