INDEX COUNT FORMULAS

[DrBourse]

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.

 

[Alex Blakenburg]

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.

=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)

 

[DrBourse]

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.

=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]

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]

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.

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