?
Solved

Formula will not work in adjacent table

Posted on 2014-10-02
4
Medium Priority
?
175 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 31

Accepted Solution

by:
gowflow earned 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

752 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