INDEX COUNT FORMULAS

DrBourse

New Member
Joined
Jul 10, 2014
Messages
12
G’Morning Ladies & Gents,



Don’t really like to bother you, I only do so after I’ve tried several times to get a formula to work…

Not having much luck with my current problem..

Before I give up altogether I thought I would “Ask for help from an Expert”..



In my “spreadsheet A”, I have a formula in a cell in column G =Index(B:B,Count(B:B)) that picks up the last figure in column B, that has been working well for years..

Now I’m trying to get that same “last Figure” from spreadsheet A into my new “spreadsheet D”.



I’ve tried formulas like =Index(B:B,Count(B:B)) & various =(IF & =IF(AND combinations, so far all I get is the standard Error window, with varying messages about Broken Formulas etc

I feel the problem lies in linking one spreadsheet cell to a second spreadsheet cell, then trying add the =INDEX(Count formula.

If anyone has the time to spare I REALLY would appreciate some help.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Alex Blakenburg

Active Member
Joined
Feb 23, 2021
Messages
354
Office Version
  1. 365
Platform
  1. Windows
I am surprised the index function consistently worked for you in the past, it only works in quite specific scenarios.
Do you have access to the Xlookup ?
If you do this is a much more robust option.
Excel Formula:
=XLOOKUP(TRUE,B:B<>"",B:B,"",0,-1)
xlookup(look for True, in the B:B<>"", return value from B:B, if not found return "", 0=exact match, -1 = start looking from the bottom up)
 
Solution

DrBourse

New Member
Joined
Jul 10, 2014
Messages
12
I am surprised the index function consistently worked for you in the past, it only works in quite specific scenarios.
Do you have access to the Xlookup ?
If you do this is a much more robust option.
Excel Formula:
=XLOOKUP(TRUE,B:B<>"",B:B,"",0,-1)
xlookup(look for True, in the B:B<>"", return value from B:B, if not found return "", 0=exact match, -1 = start looking from the bottom up)
Hi Alex,
Thanks for that info - I will try that later tonight.
My main problem is linking the 2 spreadsheets with the Index(Count formula - the following formula construction is missing something

='[8 Completed Trades for Tax Yr 2021.xlsx]4 Completed Trades'!$U:$U,(INDEX(B:B,COUNT(B:B))

Never used the XLOOKUP before, have used VLOOKUP, & HLOOKUP

Will let you know tomorrow.

Again, thanks for your help.

Cheers.
Paul K
(DrBourse is an Share Trading alias)
 

Alex Blakenburg

Active Member
Joined
Feb 23, 2021
Messages
354
Office Version
  1. 365
Platform
  1. Windows
Hi Alex,
Thanks for that info - I will try that later tonight.
My main problem is linking the 2 spreadsheets with the Index(Count formula - the following formula construction is missing something

='[8 Completed Trades for Tax Yr 2021.xlsx]4 Completed Trades'!$U:$U,(INDEX(B:B,COUNT(B:B))

Never used the XLOOKUP before, have used VLOOKUP, & HLOOKUP

Will let you know tomorrow.

Again, thanks for your help.

Cheers.
Paul K
(DrBourse is an Share Trading alias)
I had a play with your index function but it was too specific for my liking. eg
  • Count only counts numbers (which includes dates)
  • The data has to start from Row 1 to get an accurate last cell
  • If it has a heading row which is normally non-numeric you need to add +1
    (using CountA may overcome this)
  • Any alpha or blank cells in the data (non-numerics) will throw out the count.
 

DrBourse

New Member
Joined
Jul 10, 2014
Messages
12
I am surprised the index function consistently worked for you in the past, it only works in quite specific scenarios.
Do you have access to the Xlookup ?
If you do this is a much more robust option.
Excel Formula:
=XLOOKUP(TRUE,B:B<>"",B:B,"",0,-1)
xlookup(look for True, in the B:B<>"", return value from B:B, if not found return "", 0=exact match, -1 = start looking from the bottom up)
Hi Alex..
Took a while(3 attempts) but the XLOOKUP works well - Huge Thank You M8.
Cheers.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,940
Messages
5,621,728
Members
415,853
Latest member
Newlife72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top