• 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.

summary_of _the month

R

Ravindra

Member
Dear All,


I have multiple sheet of the day with name 1,2,3,4...31 that contains these column given reocrd my be vary since I just gave you a format of my query.

MISC HCFA UB DEN CORRES INVOICE

48 15 100 50 10 80

.Now I want have another sheet that contains one extra data column with these column,

So I just want to put date on accrding that it will fetch all record for these column from selected dates.


Logic:-Manually function for this purpose is- '1'.cell_name

but I want to make dynamic change on this function by filling date,,suppose as I change date 10/2/2012,it will fetch record for these column from 2 sheet name.
 
Dear All,


I have multiple sheet name 1,2,3,...31 used to maintain monthly report.

Now suppose a cell I2 in sheet no. 15 contains value 50 than I want use a function by which i can get automatic value on the basis selected sheet no.

simply this function will be used to get value of cell I2 from sheet no. 15='15'!I2

but I want to pass sheet name dynamically from a cell according dynamic purpose if I pass sheet no. 10 from another cell than it will fetch record from sheet no. 10 of cell I2 and so on.

Please give me proper solution for this.


Note:- As I self try logic ='(A2)'!I2 since sheet no. will be given from cell A2
 
Hi Ravindra,


I am not sure if I got your query properly. However, according to your last post, I assume you want to fecth data across different sheets( but the cell reference is same[I2 per your e.g.]) while dynamically changing the sheet name.


Say you have 5 sheets and sheet name are 1,2,3,4,5(Name the sheets as 1, 2, 3, 4, 5)


Place some value at A2 of every sheet.


Insert an aother sheet, place sheet name from F1 to F5


At A1, get the data validation drop down for 5 sheets' name (place cursor at A1, GO to Data>data validation> at allow select list and source box select range F1 to F5


Now at A3 write =INDIRECT(B1&"A2")


Change the sheet name from drop down, data should be automatically updated at A3.


Plz let me know if this what you are looking for....


Regards,

Kaushik
 
Dear All,


I want to fecth data across different sheets( but the cell reference is same[I2 per e.g.]) while dynamically changing the sheet name.


So I have 5 sheets and sheet name are 1,2,3,4,5(Name the sheets as 1, 2, 3, 4, 5)


Now I added a extra sheet name suppose summary and now I added two column in sumarry sheet named Sheet_Name,Value

I'll give sheet name in Sheet_Name Column in A2 and function(lookingfor)in B2(value from another sheet's cell I2)


Thanks & Regads...

Ravindra Bisht
 
Dear Ravindra,

Type C2=1000 in the sheet 1

Type C2=4000 in the sheet 2

Type C2=7000 in the sheet 3


Type A2=1 in the summery sheet

Type the formula =INDIRECT(A2&"!C"&2) in C2 in summery sheet


Now you can change the value in A2 (1,2 or 3) and get the magic!


Regards,


Muneer
 
Hi Ravindra ,


I am not sure I have understood your requirement , but you can try this :


=INDIRECT("'"&A2&"'!"&"I2")


Enter this formula in B2 and copy down.


If your reference cell changes from I2 to some other cell , you will have to replace the I2 in the above formula by the new address of the reference cell.


Narayan
 
Thanks Narayan ,Muneer both of your logic are good.

Both works fine!!!!


Keep one!!! God bless all of you here for sharing their valuable time.
 
Dear All,

Again I'm here to make my excel sheet more featured.I want to add a logic from VB in excel to make my sheet more flexiable.


I have 5 sheets and sheet name are 1,2,3,4,5(Name the sheets as 1, 2, 3, 4, 5)

Now I want to add a button in each page. Now suppose today date is 27 as already I've named all sheet according date i.e,1,2,3...31 and suppose I'm in sheet no. 5 so I soon want to reach sheet no. at 27 for this purpose how to implement button in all sheets.

Note:- User will reach at destination sheet on click event of button on the basis of today's date.


Please give me step by step proper solution.


Thanks and Regards

Ravindra Bisht
 
Hi Ravindra,


To navigate the sheets, there is a very good trick that Bobhc(one of our excel Ninjas) shared(without any VB or hyperlink feature) in this forum. I will try to explain the same as follows:


Before the sheet name starts (at the bottom left most corner of excel), there are some left and right arrows. If you right click on any one of those arrows, you will see a pop-up box containing the sheet names. Click on the sheet you want to go to...


Now, Regarding doing the same with macro,Simply copy and paste this code into a new Standard Module


Sub ShowTabs()


Application.CommandBars(“workbook tabs”).ShowPopup


End Sub


Once you paste the code in, you will see your newly added macro in your Macro dialog box (press ALT+F8 from keyboard to bring macro dialogue box).


Choose the Options button and assign a shortcut key. In this case, I have assigned Ctrl+k to the macro (you can choose any letter of your choice)


Now fire the macro by pressing Ctrl+k from keyboard.


This will activate Excel’s Workbook Tabs pop-up – showing which tab you are currently on, and allowing you to easily navigate to a different tab.


If you have more than 15 tabs in your workbook, the last entry in the list will be More Sheets.


Click on 'More Sheets' to get a full listing of all the tabs in your workbook.


Hope this helps..


Kaushik
 
Dear All,


As per my above posts I needed solution for reaching at sheet's named 1,2,3..31(shows date of assignemnt) and I added another sheet named summary in which user need to fill date and on the basis of filled date in that cell(A2) record will be fetched from choosen date sheet.

Finally I got solution for that was - =INDIRECT("'"&A2&"'!"&"I2")

However it works fine, but now my requrienment is to make more flexiable to my sheet.

So, I want to use cell(A2) as hyperlink also and if I click on that then automatically it opens specific sheet.

Suppose, I filled 10/01/2012 date in cell(A2) in summary sheet and I want to open sheet named 1 then what funcanilty should be used such that it can works for both first for passing sheet no . in =INDIRECT("'"&A2&"'!"&"I2") and being hyperlink for selected sheet.


Please give me better assumption.


Thanks and Regards,

Ravindra Bisht
 
Hi Ravindra,


Can you plz check the file from here and let us know if this is fine?


http://speedy.sh/Qjve5/Chandoo.org-Ravindra.xlsm


Please note the followings:


1) At A2(of summary sheet) dropdown(data validation) is created to hold all the days

2) Just beside the dropdown, there is a text "Go..."

3) Change the date from dropdown list and click on "Go..." to reach to destination sheet


If this fine, we can call the macro(in module1) from worksheet change event in such a way that if the value changes at A2, you can directly move to the destination sheet so that you can avoid clicking on "GO" button.


Regards,

Kaushik
 
You must log in or register to reply here.
Back
Top