• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Managing Data

S

sundeep

New Member
I am failing to prepare the excel I need....I need to cross two steps to complete my excel sheet which I need


First problem


I am entering a sales data of different product i.e. Product 1 to Product 10 every month in sheet1 and Doing its totals in Sheet2 (Monthly totals of each Product and Totals of all products)


Now the problem I am facing is when I need to add extra product i.e. Product 11 I need to change all the formulas..


Is there any way so that if I type No of products as 15... So that displays automatically Product1 in A1 and Product15 in A15 and in Sheet2 the sums of B1 to B15...


Second Problem


I have learnt from Chandoo sir article about showing only few rows and columns (http://chandoo.org/wp/2012/07/24/show-only-few-rows-columns-in-excel/)... Here also I need it to automatically hide.


For e.g. As mentioned above if I enter value 10 it should only display 10 Columns, If entered 15, It should display only 15 columns.


Please help me out.....


Thanks in advance for the help
 
Hi ,


The first problem can be solved by using named ranges , and formulae.


The second will need VBA.


Can you upload your file ? To do this , upload your file to your preferred file-sharing website ( I personally prefer SpeedyShare , http://speedy.sh/ ) , give others permission to access and download the file , and then post that access link to this forum , in this same topic.


Narayan
 
Hi Narayan


Thanks for the reply I have uploaded the file... http://speedy.sh/vSNnB/1.xlsx


I am beginner in excel please explain how to do this. So that I can do myself in future..

Though its a cake walk for you for me its a bigstone to cross... So kindly explain me the solution for problem 1 and VBA code for problem 2


Anyway thank you once again for the kind help
 
Hi ,


Thanks for uploading your file ; a few questions :


1. Do you just want that the text "Product 1" ( or "Product1" ) through "Product 20" ( or "Product20" ) should be displayed on a second sheet ? Of course , this is if you enter 20 in D4.


2. What is to be done with the headers in Sheet2 i.e. the dates 1/1/2012 and so on ?


3. In your first post , you had mentioned some totals ; in your uploaded file there are no numbers anywhere.


4. Can you explain your second requirement ? Which columns and on which sheet should be hidden ?


Narayan
 
Sorry Narayan I am not clear to you and Thanks for the quick reply


I have uploaded again. Please check here http://speedy.sh/UBjGd/1.xlsx


For your question


1) I need Product 1 through Product 20 ( i.e. Product 1, Product 2, Product 3,..... Product 20).... Yes if I enter 20 in Sheet 1 D4 it should display upto Product 20 ..... If enter 30 I should get Product 1 through Product 30


2)Yes these are dates. Actually I am trying to prepare excel sheet to view my sales data...Sale of Product 1 on January 1 and so on


3) Sorry missed that while uploading...... Now I have done


4) The second requirement is the unused Columns should be hidden i.e. If i type 20 in Sheet1 D4 then Columns from 21 to 1048576 (last column) in sheet2 should be hidden...... If I enter 30 Columns from 31 to 1048576 should be hidden in sheet 2


Sincerely, Thank you Once again for the quick help
 
Dear Sundeep,

You can change the formula for fix range to all sheet range. Such as

Write =Sheet1!A:A instead of =Sheet1!$A$1:$A$11


Regards,


Muneer
 
Hi Narayan


That's great, you have made it exactly the way I need...Thank you very much


Also please provide me the other solutions


1)So when we entered 20 in Sheet1 D4..... The Rows in Sheet2 from Row 23 should be hidden and if typed 30 from Row 33 its should be hidden


2)In sheet3 we need totals (I have applied the same formula which you have taught me) Now in Sheet3 (Column C) should sum upto 20th product automatically


and in (Column G) the Totals should be of 20 products


I have uploaded the revised file plz check it http://speedy.sh/dzjr9/Sundeep.xlsm
 
Dear Muneer


Thanks for the reply ..... But the formula you have given is not working for my problem.... Kindly go through my question once again and check this file http://speedy.sh/dzjr9/Sundeep.xlsm ....


If I am unable to wrong in understanding your formula ..... Plz guide me


Anyway Thanks for the reply
 
HI Narayan


Thanks for the solution its working fine.........


I dont need any further modifications.......I am happy no no Very happy with this
 
You must log in or register to reply here.
Back
Top