How to make Dynamic range (width) with OFFset function

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.

Attachment 444

Thanks
Rajesh

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

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

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.

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

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

Thanks
Rajesh

Thanks for letting us know.

Hi Charles,

Can I use a formula instead Application function.

Thanks
Rajesh

HI,

What do you mean?

Quote:

---

Can I use a formula instead Application function

---

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

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

Hi,

Yes you can, but if you are constantly updating the info why not just use the formula supplied by Rajan_Verma

Quote:

---

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

---

Hi

My first post!

Another version for a formula approach in B2:

=SUM(OFFSET($B2,0,1,1,LOOKUP(2,1/$C2:$T2,$C2:$T2))) and copy down.

Also accounts for cells not filled in continuously.

Kevin

Thanks guys.:)