Solved

Formula will not work in adjacent table

Posted on 2014-10-02
4
167 Views
Last Modified: 2014-10-03
Hi everyone,

below is what 'goflow' experienced with the attached file. If anyone knows how to solve this, please post.

The issue in on sheet 'Current Game 2' columns V and W. For some reason the formulas below will not work there.

The link to the original post is:
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28526375.html#a40352615

Thanks,

Goglow Said:

I added them in the attached file at the bottom of the table in the green section as I could not get the formula to display results in col VW like advised before.

 The formula for the team is
 =IF(INDIRECT("E" & 4*ROW(E5)-15)-INDIRECT("M" &4*ROW(M5)-15)<0,INDIRECT("B" & 4*ROW(B5)-15),INDIRECT("J" & 4*ROW(J5)-15))

 The formula for the results is
 =ABS(ROUND(INDIRECT("E" & 4*ROW(E5)-15)-INDIRECT("M" &4*ROW(M5)-15),2))&" - "&ABS(ROUND(INDIRECT("E" & 4*ROW(E5)-15+2)-INDIRECT("M" &4*ROW(M5)-15+2),2))

 When you fix the issue of getting the results in col V and W then you take these 2 formulas that you copy from here and paste into cell V6 and W6  and from there you drag them down.
EE-gamestats-ERA.xlsm
0
Comment
Question by:Pedrov664
  • 2
4 Comments
 
LVL 26

Expert Comment

by:pony10us
ID: 40357147
I am not sure I follow. I took the formulas from the original post and copied them into V6 and W6, copied them down through V21 and W21, and the results were the same as Goglow had in the green section (E71-J82).

See attached.
Copy-of-EE-gamestats-ERA.xlsm
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40357263
Well What I noticed is that you need to delete the whole columns in this area where your table is like col S till AA as surely there are some old codes that are remembered by Excel somehow that is keeping formulas from displaying. and by putting the formula in the new columns created it will work fine.

here is the workbook attached.
gowflow
EE-gamestats-2014-V01.xlsm
0
 

Author Comment

by:Pedrov664
ID: 40359537
Pony10us,

It seems you got the correct results. Don't know why though.

Goflow,

I had to delete an recreate the V:W to get this to work. But it did work.

Thanks,
Pedro
0
 
LVL 26

Expert Comment

by:pony10us
ID: 40359558
I don't know why either.  Just lucky that way sometimes.  :)

Glad you got it working though.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now