Solved

Formula will not work in adjacent table

Posted on 2014-10-02
4
169 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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.

785 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