Substitute Formula Value in Cell Address

[GeyikBaba]

The simplified situation is:

a cell has the formula =SUM(A1:A100)

I need to calculate the row number in the 2nd argument so as to get the last row populated.

The formula =MATCH(LOOKUP(2,1/($A:$A<>""),$A:$A),$A:$A,0)+ROW($A:$A)-1 in a different cell works correctly.

Say I have the formula above in cell B1.

How do I plug in the value from B1 so as to substitute it for the hard coded value 100 in the =SUM() function?

Many thanks

 

[Fluff]

Why not just something like

=sum(A1:A1000)

rather than worrying about the last used row.

 

[GeyikBaba]

Why not just something like

=sum(A1:A1000)

rather than worrying about the last used row.

Thanks, the blanks rows cause problems. My issue is a little more complex. I just want to know whether there is a way to substitute in a value from a different cell in place of a hard coded address. This would be a form of macro substitution.

 

[Alex Blakenburg]

Can you update your profile to show what version of Excel you are using, the best solution often varies depending on the version of Excel.
Your Match formula is unreliable. Firstly it Spills in MS 365. Secondly it get the last value in column A but then uses Match to search from the top for that value.
If the value appears in an earlier row it will return that row and not the last row.

You also mention using a macro. If you use a macro you would not use a formula in B1 to determine the last row, you would most likely use End(xlUp). So please clarify your process and what you are trying to do. If you have any existing code please post it here using the VBA code button.

 

[GeyikBaba]

It's part of MSFT Office 2016 Plus.

I should not have used the term 'macro substitution'. It is outdated and suggests VBA. In fact, this would be pretty esay to solve with VBA, but I don't want to go there because this spreadsheet will be used by others who would never be able to fix it if a problem came up. It would be easy for me to do this manually every time, but I will not be the one using this spreadsheet.

I just want to know whether there is a way to substitute a value from a cell into an address.

If a formula value in cell B1 is, say, 65, is there a way to write the formula +SUM(A5:Axxxx) so that it in effect reads =SUM(A5:A65)?

Thanks
Mike Thomas

 

[Fluff]

It's part of MSFT Office 2016 Plus.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

You can use

=SUM(A5:INDEX(A:A,B1))

but I really do not see the point in making things more complicated for yourself, or others.

 

[GeyikBaba]

Thanks very much, I updated my profile. The formula =SUM(A5:INDEX(A:A,B1)) worked perfectly. I'll dig through it, but it gives me a type of notation that is helpful in a lot of cases.

Mike Thomas

 

[Fluff]

Glad we could help & thanks for the feedback.