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

Create Reference from a cell formula

0

Hi!

I have a program on excel to calculae recipes and ice cream items,

so in sheet 1 I have a Ingredients list with all ingredients and the prices,

now in sheet 2 I have the colculation for the resipes, and then I put the name of the recipe in sheet 1 in column A like a ingredient and the price in column B,

now what i want to do is, Instad to rerence the cell in column A to the title of the recipe, and the cell in column B to the price of the recipe, I want when I reference the cell in column A, should the cell in column B be equals to where the cell in column A is referenced, and then a OFFSET function to the cell of the price,

like this 

lat's say, the title name of the recipe is in cell E1, so in A1 I make =E1,
and in H20 is to total price of the recipe, so in B1 I make =H20,
so what i want is, instead to make in B1 =H20 I want it should go automatically, so in B1 I put something like this =IF(A1=E1,OFFSET(E1,19,3),"")
but I want where ever A1 is linked, should B1 be linked to the same cell with a OFFSET(?,19,3)

excel
MS Office Version: 2013
posted by: JAExcel (rep: 10) on Mon Aug 7, 2017 at 1:10 pm
Answer
Discuss

Discussion

This sounds like a task for VLOOKUP, not OFFSET. Read up on the function, but if that doesn't solve your problem please post a mockup of your workbook so that we can see where the data are.
Variatus (rep: 4889) Aug 7, '17 at 8:29 pm
Add to Discussion

Answers

0
Selected Answer

I put this in B1 and I got the price for which recipe is in A1

=OFFSET(INDEX(sheet2!E1:E2000,MATCH(A1,sheet2!E1:E2000,0)),19,3)

posted by: JAExcel (rep: 10) on Tue Aug 8, 2017 at 1:12 pm
Discuss
0

I don't think I quite understand this question.... just go to sheet 2 in cell B1 and type = and then go to sheet 1 and click cell A9 and hit Enter.

posted by: don (rep: 1989) on Mon Aug 7, 2017 at 4:13 pm
Discuss

Discussion

lat's say, the title name of the recipe is in cell E1, so in A1 I make =E1,
and in H20 is to total price of the recipe, so in B1 I make =H20,
so what i want is, instead to make in B1 =H20 I want it should go automatically, so in B1 I put something like this =IF(A1=E1,OFFSET(E1,19,3),"")
but I want where ever A1 is linked, should B1 be linked to the same cell with a OFFSET(?,19,3)
JAExcel (rep: 10) Aug 7, '17 at 5:45 pm
Add to Discussion


Answer the Question

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