• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

Index / Match Function

Hi,

if you look at Column J of the attached file you can see it is picking up tax Credit from Column A (13.88 ETC)

I need this formula to be changed to pick up gross (one row up in ColA)

In K you can see it is picking up the holdings from ColA.

The ones i have highlighted re are incorrect because they dont pick up enough characters, so the formula needs to be adjusted to pick up numbers that would be a over 8 figures

If anyone can adjust for these two that would be great!

Thanks
Seamus
CP-Rep1-1-.zip
0
Seamus2626
Asked:
Seamus2626
  • 4
  • 2
1 Solution
 
Eric ZwiekhorstSAP Business ConsultantCommented:
Dear

=TRIM(MID(INDEX($A:$A;$B7+9);36;9)) in the first J cell

Kr

Eric
0
 
Eric ZwiekhorstSAP Business ConsultantCommented:
for columns K to adjust the amount of numbers to be picked up you have to change

=TRIM(MID(INDEX($A:$A;$B9+20);36;10)) the last number in this formula The 10 indicates you want to copy 10 characters( numbers)

If you want more change into 11, 15 or 20...


KR

Eric
0
 
Eric ZwiekhorstSAP Business ConsultantCommented:
Dear seamus,

I adjusted all cell in columns J and K

Kr

Eric
CP-Rep1-1-.xls
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Seamus2626Author Commented:
Hi Zwiekhorst,

Thank you for that.

I get how it works now!
0
 
barry houdiniCommented:
But that still doesn't pick up the full figure, e.g. K9 should be 1,695,279, I believe. Each of the relevant cells appears to have a colon at position 22 then the rest of the data is spaces and the figure so to get the full figure you might as well take everything after character 22 and trim it, e.g. in K7 copied down

=TRIM(MID(INDEX($A:$A,$B7+20),23,100))

If you want to convert that to a number (it's currently text) then add +0 to the end of the formula

regards, barry
0
 
Eric ZwiekhorstSAP Business ConsultantCommented:
Hi Seamus,

Thanks for the points and the grade

kr

Eric
0
 
Seamus2626Author Commented:
hey Barry, your right, but i worked it out from the Col J adjustment so all good, thanks for the update though!

Cheers,
Seamus
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now