Solved

# Formula will not work in adjacent table

Posted on 2014-10-02
165 Views
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
Question by:Pedrov664
• 2

LVL 26

Expert Comment

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

gowflow earned 500 total points
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

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

I don't know why either.  Just lucky that way sometimes.  :)

Glad you got it working though.
0

## Join & Write a Comment Already a member? Login.

### 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.

#### Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!