Solved

Formula will not work in adjacent table

Posted on 2014-10-02
4
165 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I don't know why either.  Just lucky that way sometimes.  :)

Glad you got it working though.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
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 in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

762 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

11 Experts available now in Live!

Get 1:1 Help Now