Variabele selectie

Hi welcome to the forum

I speak afrikaans, which is fairly close to dutch, and if I understand you correctly, you want to find the last cell with a value in it? If so, then try this...
=INDEX($A$1:$A$10,MATCH(0,A1:A10,-1))

If that was not what you wanted, please ask in english, or ask for your thread to be moved to the non-english forum, and upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Welkom bij de forum.

Als u echt de nummer van B1 gebruiken wilt, kunt u deze formule gebruiken (in het Engels, sorry - ik heb geen NL versie in werk):

Formula:

Please Login or Register  to view this content.

Maar dan moet u de laatse gebruikte cel in B1 invullen, wat kan vergeten zijn.
Dus wordt het mischien beter uw formule met die van FDibbins te combineren:

Formula:

Please Login or Register  to view this content.

Maak de $A$100 zo groot als nodig is - als u soms tot rij 500 gebruiken, dan maak het $A$1:$A$1000 enz.

Hierbij een bestand met de formulen - dan kunt u hen in het NL zien: Sum to last row used in column A _ voor RobFontaine.xls

(Het spijt me als er fouten met mijn Nederlands zijn - het is niet mijn moedertaal.)

All,

Thanks for your help. I will continue in English because it's more easy for you, I think.
Aardigspook, when I use your solution it works perfectly but the SUM command was only for my example, to makes it easy.
In real live I am going to use the DIRECT command in a 6st order polynomial matrix formula:

{=LINEST(B3:B23;A3:A23^{1\2\3\4\5\6};TRUE;FALSE)}

B23 is the one who is variable. The length of the column is visible in cell E24.

So, I changed the formula to:

{=LINEST(B3:INDIRECT("B"&E24);A3:A23^{1\2\3\4\5\6};TRUE;FALSE)}

But this gives the error: #REF.

The content of cell E24 is: =COUNTIF(A3:A250;">0")+1
This gives the length of the column. In the INDIRECT solution I have to use the value of E24 not the content. Maybe that's the problem.

Do you have any suggestion?
Both guys, thanks!

Rob

EN:

I think the problem is that E24 is giving the wrong result - because column A is starting in row 3, you need to add 2 not 1:

Formula:

Please Login or Register  to view this content.

then your formula will work fine:

Formula:

Please Login or Register  to view this content.

I've attached a file showing it working for me - with random numbers in A3:B23

NL

Het probleem ligt in E24, denk ik - omdat kolom A begint in rij 3, moet je +2 gebruiken in plaats van +1:

Formula:

Please Login or Register  to view this content.

Dan werkt u uw formule goed:

Formula:

Please Login or Register  to view this content.

Hierbij een bestand: Indirect _ voor Rob Fontaine.xlsx

mvg,

Iets meer: u moet ook, denk ik, INDIRECT gebruiken voor kolom A:

Formula:

Please Login or Register  to view this content.

Ik ben niet zo goed in 'polynomial matrices' dus misschien heb ik dat fout - maar in mijn bestand werkt het anders niet.

Hierbij het bestand met deze formule: Indirect v2 _ voor Rob Fontaine.xlsx

mvg,

Okay, there are two solutions I can see for E3 and E4:

Nr 1 - using INDIRECT
Change the formula in E24 to this (to allow for any zero values in column A):

Formula:

Please Login or Register  to view this content.

Then use these formulae in E3 and E4:

Formula:

Please Login or Register  to view this content.

These give the same results as the formula you have in those cells at the moment, with A3:A23, B3:B23 and C3:C23 manually entered.

Nr 2 - using Dynamic Named Ranges
First, set up some Named Ranges for A3:Axxx, B3:Bxxx and C3:Cxxx. Select A3:A23 (or A3:A## - it doesn't actually matter). In the box to the left of the formula box (where it should be showing 'A3' now), type 'NR_Data_x' (without the inverted commas). Repeat with B3:B23, using 'NR_Data_y1', then with C3:C23, using 'NR_Data_y2'.
Now go to 'Name Manager' on the 'Formulas' tab. (In het Nederlands - 'Namen beheren' op de 'Formules' tab.)
Select the 'NR_Data_x' Name. In the 'Refers to' box at the bottom, over-write what's there with this:

Formula:

Please Login or Register  to view this content.

You can reduce $A$10000 to $A$1000 or $A$100 if your data will never go beyond that row.
Repeat for 'NR_Data_y1' and 'NR_Data_y2', replacing the 'A' references with 'B' and 'C'.
These Dynamic Named Ranges will automatically expand/contract with the number of rows of data - in this case, to the last numeric entry in the range A3:A10000. So you now don't need cell E24, unless you need a visual reference of how many values you have (if you do, then change it as in Nr.1 above). You certainly don't need to use INDIRECT.

Instead, change the formulae in E3 and E4 to these:

Formula:

Please Login or Register  to view this content.

A note on naming of Named Ranges: there are some limitations on what you can call them - you can't use certain 'reserved' names such as 'Print_Area', for example, and can't use anything which could be a cell reference (so CAB123 can't be used). They must start with a letter, backslash or underscore and can only include numbers, letters and full stops (.) - no spaces. I therefore always start my Names with 'NR_' to indicate 'Named Range' then use underscores to label them further - you may have another system you want to use.

As I hinted at above, my 25-years-out-of-date first year university maths isn't up to the polynomial calculations you're doing in this file - but the formulae I've provided above give you the same results as your manual ones, so I hope that helps.
Here's the file with the Dynamic Named Ranges working (on sheet BM 30-14 only): Polynomial generator _ AS.xlsx