How to make Dynamic range (width) with OFFset function

[Rajesh Kr Joshi]

Hi,

In the attached sheet i need to sum the daily sales record for indivusal sales person. Is there a way to make the dynamic width (single row) using Offset function of any other formula so that it only sum the data till the last non blank cell.

Dynamic Width.xlsx

Thanks
Rajesh

[Charles]

Hi,

Your request could be done via code.
I do not know if a formula will do this.


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

[Rajesh Kr Joshi]

Thanks, Charles.
A code will also work for me.

[Charles]

Hi,

In this copy I did not use a formula for the worksheet.
I set the code to provide the Value of each Total.
I have a "Interface " sheet with a Button. When you click it the code will run.
You can the select the sheet to check the result.

[Rajan_Verma]

Two solution are here for you
if you filled the cell continously ,
=SUM(OFFSET(Sheet1!C2,,,1,COUNT(Sheet1!C2:T2)))

if you fill the cells not continously
=SUM(C2:INDEX(C2:T2,1,MATCH(999999999,C2:T2,1)))

Rajan

[Rajesh Kr Joshi]

Thanks Rajan and Charles, both the solutions are working perfect.

Thanks
Rajesh

[Charles]

Thanks for letting us know.

[Rajesh Kr Joshi]

Hi Charles,

Can I use a formula instead Application function.

Thanks
Rajesh

[Charles]

HI,

What do you mean?

Can I use a formula instead Application function

Are you anting to use the "Formula" in the vba code?
Or in the worksheet

[Rajesh Kr Joshi]

Thanks for your reply.
if possible In the VB code , else in the worksheet will also work.