Use cell value in vba formula

**Spyderwoman** · 12-08-2015, 03:01 PM

I have 3 lines for each account and I have many stores (tabs) so I would like this in VBA if possible
the year columns ("E") lines are:
2015.00 is actual
2015 is a line to change/manipulate to see how line 2016 reacts
2016 contains formulas based on either the line above or the budget tables ,the reference budget tables that can also be changed.
Column ("W") is my helper column. I use this column to reference back to budget tables using index/match.
My question/problem is that I want the number from gallons sold to use in formula on PPG-Premium without manually looking up row/column, current formula =F164 * .1874. New formula to (=1589.98 * .1874)
Row 2016 will be same as above + PPGI. The worksheet has the formulas in cells.
I'm not sure, If Helper ("S") are same/equal use the gallons sold* PPG. Loop through each period.
I hope I explained this well.

**dflak** · 12-08-2015, 05:04 PM

You might want to check the file you downloaded. I don't see a column W and I don't see any formulas. Maybe some of what you are saying will make more sense when I see some formulas. However, at the moment I'm only vaguely getting the requirement.

My question/problem is that I want the number from gallons sold to use in formula on PPG-Premium without manually looking up row/column, current formula =F164 * .1874. New formula to (=1589.98 * .1874)

Can you explain this part a bit more?

However, looking at the data, this is an application that is just screaming to be done in a database.

**Spyderwoman** · 12-08-2015, 06:05 PM

My bad, my old spreadsheet had column W, this one has column S. I would love to do in a database however, it's not possible.
I hope I can explain this, not even sure if it is possible. Look at cell F64 PPG-Premium, the formula has to reference cell F164 Premium gallons sold in order to compute. I need that variable (gallons) to find the gallons account that matches the PPG account. Current formula "=F164 * PPG", I would like a way to figure out what F164 is without manually entering F164 * PPG, something like, "if Premium Gallons sold Helper account = PPG Premium Helper account then F164 is my variable".

**dflak** · 12-09-2015, 05:29 PM

Still noodling this one. How did you determine that Cell F164 was the cell to use? Where is the premium gallons sold figure? What is this Premium Helper Account that you are talking about?

Are you looking to set up a table off to the side and do a lookup against it?

**Spyderwoman** · 12-09-2015, 06:36 PM

Cell F164 is Premium Gas therefore it matches PPG Premium, the Premium gallons sold figure is cell F164. The helper column is the last column.

**dflak** · 12-10-2015, 10:38 AM

Excel is definitely the wrong tool to manage this information. Even an Access Database would be much better.

Anyway, I think I'm getting the picture. You somehow have to link PPG - Premium with Premium Gallons Sold. I suggest using an alias table like the one in the Aliases Tab in the attachment. Use VLOOKUP to find the string you want, then find the second instance of Premium Gallons Sold (This is actually cell F163, not F164 according to the formula in Cell 64.

To find the Nth occurrence of an item takes an array formula =LARGE(((Range=Value)*ROW(Range)),COUNTIF(Range,Value)-Number+DataStartRow)

In this case the value will be the result of the VLOOKUP the Range is D:D and the DataStartRow is 1.

I took the liberty of hiding the other rows and columns to make navigation and testing easier. I also changed the values in Cells F162 and F163 to 1,000 and 2,000 respectively to make sure I'm picking the right row with my formulas. I can't have them be both the same.

In helper cell T64 I have the following formula: =VLOOKUP(D65,Table_Aliases,2,FALSE)
In helper cell U64 I have the following formula: =LARGE((($D:$D=T64)*ROW($D:$D)),COUNTIF($D:$D,T64)-2+1)
In helper cell V64 I have the following formula: =INDEX($A$1:$S$177,U64,6)

This is the value contained in cell F163. I took these three formulas and substituted the helper cells to get one formula using the original cells. The result is shown in cell W64.
=INDEX($A$1:$S$177,LARGE((($D:$D=VLOOKUP(D65,Table_Aliases,2,FALSE))*ROW($D:$D)),COUNTIF($D:$D,VLOOKUP(D65,Table_Aliases,2,FALSE))-2+1),6)

And this only replaces the first term in your original formula:
=IFERROR((F163*(INDEX(PPG,MATCH($S65,PPGSite,0),MATCH(F$1,PPGPeriod,0)))*-1),0)
so it becomes:
=IFERROR((INDEX($A$1:$S$177,LARGE((($D:$D=VLOOKUP(D65,Table_Aliases,2,FALSE))*ROW($D:$D)),COUNTIF($D:$D,VLOOKUP(D65,Table_Aliases,2,FALSE))-2+1),6)*(INDEX(PPG,MATCH($S65,PPGSite,0),MATCH(F$1,PPGPeriod,0)))*-1),0)

but wait!
The 6 just happens to match the column in which the formula is , so we can replace it with COLUMN() so the formula can be copied across.
So the final formula is:
=IFERROR((INDEX($A$1:$S$177,LARGE((($D:$D=VLOOKUP($D65,Table_Aliases,2,FALSE))*ROW($D:$D)),COUNTIF($D:$D,VLOOKUP($D64,Table_Aliases,2,FALSE))-2+1),COLUMN())*(INDEX(PPG,MATCH($S64,PPGSite,0),MATCH(F$1,PPGPeriod,0)))*-1),0)

entered as an array formula, of course.

[Edit] I messed up the VLOOKUP. I originally had =VLOOKUP(D65,Table_Aliases,2,FALSE) It should be =VLOOKUP($D64,Table_Aliases,2,FALSE) to reference the correct row and to make sure the formula copies across accurately. I fixed this in the final formula.

**Spyderwoman** · 12-10-2015, 11:35 AM

I agree with you, access would certainly be better. Let me take a look at this and I'll get back with you. I appreciate your help so far. thank you

**dflak** · 12-10-2015, 11:39 AM

If you need help with the access side, give a shout here: http://www.utteraccess.com - that's where I normally hang out. There are a lot of experts there.

I am not as good in access as I am in excel, but I'm good enough especially with the database design.

**Spyderwoman** · 12-10-2015, 02:14 PM

Thank you dflak, works great. In my spare time, I'll visit you in your hangout. Now if I can figure out how to mark this as solved and give you a big thumbs up. Again, many thanks

Use cell value in vba formula

LinkBack

Thread Tools

Rate This Thread

Display

Use cell value in vba formula

Re: Use cell value in vba formula

Re: Use cell value in vba formula

Re: Use cell value in vba formula

Re: Use cell value in vba formula

Re: Use cell value in vba formula

Re: Use cell value in vba formula

Re: Use cell value in vba formula

Re: Use cell value in vba formula

Thread Information

Users Browsing this Thread

Similar Threads

VBA to enter formula in cell not calculating...showing formula in cell

[SOLVED] Enter formula result from one cell into a COUNTIF formula in another cell. Help please!

Auto-fill a cell based on a drop down selection in one cell & a formula in another cell

[SOLVED] Retaining formula cell references when copying a formula to a different cell

populate cell with formula referenced from array, insert adjacent cell value into formula

Change Cell Font Color if Cell Contains Formula Referencing Another Cell

Formula to display cell address the cell adjacent to the highest value cell

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions