SOLVED

selecting data from variable row length

%3CLINGO-SUB%20id%3D%22lingo-sub-3293695%22%20slang%3D%22en-US%22%3Eselecting%20data%20from%20variable%20row%20length%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3293695%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20use%20data%20from%20row%204%20last%20Cell%20(M4)%20and%205th%20last%20Cell%20(H4)%20entry.%3C%2FP%3E%3CP%3EBut%20subsequent%20rows%20(5%2C%206%2C%20etc.)%20do%20not%20have%20equal%20data%20range.%3C%2FP%3E%3CP%3EWhat%20function%20can%20I%20enter%20in%20Cell%20B4%2C5%2C6%2Cetc.%2C%20to%20always%20find%20last%20Cell%20and%205th%20last%20Cell%20data%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22drdree73_1-1650875761653.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F366484iC6EA3EEC6623CC5D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22drdree73_1-1650875761653.png%22%20alt%3D%22drdree73_1-1650875761653.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3293695%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3293929%22%20slang%3D%22en-US%22%3ERe%3A%20selecting%20data%20from%20variable%20row%20length%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3293929%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1367202%22%20target%3D%22_blank%22%3E%40drdree73%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENo%20idea.%20Formulas%20have%20nothing%20to%20do%20with%20showing%20sheet%20tabs%20or%20not...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3293899%22%20slang%3D%22en-US%22%3ERe%3A%20selecting%20data%20from%20variable%20row%20length%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3293899%22%20slang%3D%22en-US%22%3E%3CP%3ESince%20applying%20the%20formula%2C%20my%20excel%20tabs%2Fsheets%20have%20become%20hidden%20(most%20of%20the%20time).%20They%20are%20there%2C%20but%20invisible.%20Show%20tabs%20is%20on.%20Any%20idea%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22drdree73_1-1650891608827.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F366553i6A16DFB3485117A7%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22drdree73_1-1650891608827.png%22%20alt%3D%22drdree73_1-1650891608827.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22drdree73_0-1650891568083.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F366552i468B873838B16112%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22drdree73_0-1650891568083.png%22%20alt%3D%22drdree73_0-1650891568083.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3293789%22%20slang%3D%22en-US%22%3ERe%3A%20selecting%20data%20from%20variable%20row%20length%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3293789%22%20slang%3D%22en-US%22%3Eindeed%20indeed%20%3Aface_with_tears_of_joy%3A%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3293747%22%20slang%3D%22en-US%22%3ERe%3A%20selecting%20data%20from%20variable%20row%20length%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3293747%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1367202%22%20target%3D%22_blank%22%3E%40drdree73%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20visiting%20a%20forum%20such%20as%20this%20one%20%5Bgrin%5D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3293727%22%20slang%3D%22en-US%22%3ERe%3A%20selecting%20data%20from%20variable%20row%20length%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3293727%22%20slang%3D%22en-US%22%3EOmg%2C%20it%20works.%20Thank%20you%20very%20much.%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20could%20a%20%22normal%22%20user%20ever%20find%20such%20solutions%3F%3F%20%F0%9F%A4%B7%E2%80%8D%3Amale_sign%3A%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3293700%22%20slang%3D%22en-US%22%3ERe%3A%20selecting%20data%20from%20variable%20row%20length%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3293700%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1367202%22%20target%3D%22_blank%22%3E%40drdree73%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20B4%2C%20if%20you%20have%20Microsoft%20365%20or%20Office%202021%3A%3C%2FP%3E%0A%3CP%3E%3DLET(m%2CMATCH(9.99999999999999E%2B307%2C4%3A4)%2CINDEX(4%3A4%2Cm)%2FINDEX(4%3A4%2Cm-4))%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20an%20older%20version%3A%3C%2FP%3E%0A%3CP%3E%3DINDEX(4%3A4%2CMATCH(9.99999999999999E%2B307%2C4%3A4))%2FINDEX(4%3A4%2CMATCH(9.99999999999999E%2B307%2C4%3A4)-4)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi, 

 

I use data from row 4 last Cell (M4) and 5th last Cell (H4) entry.

But subsequent rows (5, 6, etc.) do not have equal data range.

What function can I enter in Cell B4,5,6,etc., to always find last Cell and 5th last Cell data?

 

 

drdree73_1-1650875761653.png

 

 

6 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

@drdree73 

In B4, if you have Microsoft 365 or Office 2021:

=LET(m,MATCH(9.99999999999999E+307,4:4),INDEX(4:4,m)/INDEX(4:4,m-4))

Fill down.

If you have an older version:

=INDEX(4:4,MATCH(9.99999999999999E+307,4:4))/INDEX(4:4,MATCH(9.99999999999999E+307,4:4)-4)

Omg, it works. Thank you very much.

How could a "normal" user ever find such solutions?? 🤷‍

@drdree73 

By visiting a forum such as this one [grin]

indeed indeed

Since applying the formula, my excel tabs/sheets have become hidden (most of the time). They are there, but invisible. Show tabs is on. Any idea?

 

drdree73_1-1650891608827.png

 

drdree73_0-1650891568083.png

 

@drdree73 

No idea. Formulas have nothing to do with showing sheet tabs or not...