Is it possible to access other tabs on my spreedsheet dynamically? =COUNTIF(July!$F$4:$F$200,$H7) not dynamic
A1 = July. =COUNTIF(A1!$F$4:$F$200,$H7) dynamic. but how is this done in Excel?? Is it possible?
I'm pulling data into some graphs from multiple sheets. The data I'm after is in the same place in each of the sheets, and the sheet name is varying only in that they refer to different dates, such as "01-11-13", 02-11-13 etc...
How would I go about dynamically picking up the sheet name. and referring to it?
For example if I have a column with all the different sheet names in it:
Col AA:
31-10-13
01-11-13
04-11-13
etc!
I have a challenge I'm trying to surmount. I have an excel sheet in which I want to be able to select Zone codes from a drop down, click on a link "search" which takes me to a place in the document that has been named according to the Zone code i pick in the drop-down. I want to achieve this using only formulas as i'm distributing the file to a wide audience who are not versed enough with computers to know how to enable macros.
View 3 Replies View RelatedI'm reading data, from specific cells off a closed workbook. When the sheet that needs said cell data is activated, it automatically opens the workbook and references the sheet nessecary. The issue I've come across, is I now need to access another workbook (Easy to open) with 12 sheets 1 for each month, and only read from the worksheet of the actual Month...
Kind of lost on how to possibly make this work. I basically need something like:
=location/[workbook.xls]Month(Today())!cell
Is there a way to use the "value" of a name in a drop-down list to reference a sheet tab name in a formula?
A1 has a drop down list. When a name (Bob's Sales) is selected from the drop down list, B1 shows the value of cell D5 from sheet "Bob's Sales".
I'm a bit over my head on this one. I want a formula that does the following: Look at the date I put in on the last tab and find the correct date on the other tabs. Using that date as the column I want it to return the correct row for the data.reference.
I am using the HLOOKUP function. I'm not even sure this is the right function. Ont the workbook attached I'm trying to get the data on the Totals tab to come from the Sept Wk 1 through Sept Wk 5 tabs. The formula I tried to use is on the Totals page C7.
I am creating a spreadsheet to track customer data and need to reference certain cells in individual customer tabs to a master sheet that contains every customer. Each customer will have his own tab, each tab having the same layout, and each client will also have their own row on one master sheet within the same workbook.
My question is, is it possible to reference the specific cells in the clients individual tab to the master list once, and then everytime I copy a new client tab, the referenced cells in that tab are immediately referenced to the corresponding client (new) row in my master sheet within the same workbook?
I searched but didn't find exactly what I needed. I have a workbook with 31 sheets. It is a price guide with each category on a separate worksheet. I figured out how to list the sheets on a separate tab. What my client wants is the ability to:
1. select certain categories for printing, the ToC, Cover and backcover pages have to print in every case
2. the ToC has to change depending on the sheets selected.
I'd rather write some code and give him an an easy command button rather then teaching him how to select non-concurrent sheets and printing only active sheets.
What I'm really stuck on is the updating of the ToC with active sheets only (category and starting page which changes depending on pages selected).
I have been trying to get Excel (2007) to change the colour of tabs to match other tabs in the workbook
Using this code I get the colour code of the current tab
Code:
x = ActiveSheet.Tab.ColorIndex
But when I use this value in a procedure like this:
Code:
ActiveSheet.Tab.ColorIndex = x
The tab is a different colour!
I have a summary page that includes the titles for each tab within the excel 2003 workbook. I want to use the names of the tabs in the summary page and create it into a formula to lookup fixed cells within the various tabs. Sorry for not uploading an excel doc but I was at work earlier and the thread did not load for some reason, so I am reposting it.
View 3 Replies View RelatedI'm using Excel '03SP2 on Win 2000sp4. I believe I have a case where old tabs originally written in Excel ' 97 behave differently than new tabs in the same workbook, which is saved in 2003 format.
Specifically, the formula ="PLAN" & budyr-1 where budyr is a named range on a different tab containing 2008 . This formula yields PLAN 2007 on a new tab and #VALUE! on an old tab.
How do I get Excel to update the old tabs so that they behave as Excel 2003 tabs?
I have a link
='S:2014DomesticATL[ATL-BP Eval-2014-04.xls]Business Partner Evaluation'!F$13
Now i have to compile 3 years of data for 200 airports so changing the month and year for every airport would be a challenge. I can do Replace all and change the year and month but instead of that i was thinking if i could have the year and month on one row and connect the links to that cell so that whenever i drag the cell my link changes
im looking at
2014DomesticATL[ATL-BP Eval-",D2",.xls]Business Partner Evaluation'!F$13
where in D2 i will have 2014-04 . But this formula doesnt seem to work. Is there anyway i can do this ?
I have a list of about 3,000 names and places of work, in two columns. What I want is to have the place of work in the first column, and then all the employees along that row.
There must be a simple solution to this, but I have been banging my head on my desk all morning trying to work it out. All the places of work are sorted, if that makes any difference.
I have a cell range of L3:N3 on Sheet 1 and on Sheet 2 in A1 i have the value of N3 of Sheet 1.
Now, if I move L3:N3 to eg. R10:T10 I want the value A1 ( Sheet 2 ) to be updated to T10 ( Sheet 1 )
Right now, it wont update dynamically, value of A1 stays at N3...
I basically am trying to just reference another cell (from a pivot table) from another sheet. ='SHEETNAME'!G5
But I Need the G to change... I basically need
='SHEETNAME'! + E29 + 5 to equal
='SHEETNAME'!G5 assuming G is in E29 but am not sure how to do this.
The other option is to use =GETPIVOTDATA("Sum of Amount_19",'SHEETNAME'!$A$3,"Label","Title") but I need "Sum of Amount_19" to change based on another cell, lets say F29.
I'm trying to dynamically set the print area for a given worksheet. I use the code below and it works perfectly when it's executed upon opening the tab; however, the worksheet has filters which may lengthen/shorten the required print area.
Is it possible to execute the code below when the user selects print rather than when opening the tab?
[Code] ......
In the attached sample workbook I am trying to add the vendor number down the column H for each PO. I have copied a few of the numbers into the column, (in red), to show the desired result.
Basically, I will have a report daily with a variable number of suppliers, and variable PO's for each supplier. I need to add the vendor # to each row.
I am having a problem with a VBA macro I am trying to write. All I want to do is check all rows in a worksheet and group rows from the last bold row to the next bold row.
I seem to have the conditional logic correct, however, instead of grouping rows separately in each loop, it keeps joining them together.
I'm am trying something that is still a little over my head...
Normally, I merge cells the easy way:
In my worksheet cell A1 represents a financial reporting period from 1 to 12. It also represents the number of columns I need to sum in order to arrive at the desired period totals in this income statement worksheet. How can I get Excel to automatically change my SUMPRODUCT function as the value in cell A1 is changed from 1 to 12?
Here is a sample cell formula:
=SUMPRODUCT(ISNUMBER(MATCH(GL1SHL.XLS!$A$2:$A$332, {"4620","5220"},0))*(GL1SHL.XLS!$CE$2:$CE$332)*(GL1SHL.XLS!$I$2:$T$332))
The above formula represents the totals for period 12 for General Ledger accounts 4620 and 5220.
GL1SHL.XLS! references the workbook that has the financial data exported from my G/L application.
$A$2:$A$332 is the column which contains the G/L account number to be matched.
$CE$2:$CE$332 references a cell that resolves to the value 1 or -1 so that expenses which are exported as positive values become negative.
$I$2:$T$332 represents the 12 columns and 332 rows that contain all the financial data necessary to calculate the income for period 12 with each row representing a different G/L account's 12 periods of financial data. I need a method to dynamically replace the 'T' in $I$2:$T$332 and resolve it based on the value in cell A1. So if A1=1 then 'T' becomes 'I' and if A1=2 then 'T' becomes 'J' etc.
I would like to do this as a macro substitution instead of having to make 12 worksheets, one for each period, or instead of having 12 nested loops.
Is it possible to run a macro that will delete the LAST X rows? It must be dynamic, and now be fixed to a specific row.
View 8 Replies View RelatedI am trying to format a cell that contains the weekday number to display the name of the day but still keep its weekday number value. For Example, a cell contain the value of 2. This value is Monday as per Excel Weekday() value. I want to still keep the value as 2 but I want it displayed as Mon. If the value is 5 then I want it displayed as Thr.
Is there a way when creating a custom format display for a number that I can state 1="Sun"; 2="Mon"; 3="Tue"; 4="Wed"; 5="Thr"; 6="Fri"; 7="Sat".
I have a problem to remove duplicates in Excel.
Ex:1
I have two columns Week No and Count
W1 50
w1 52
w2 25
w3 30
Here I can Remove weekly No but Later they will update more weeks. I want automated formula to solve this problem. Values I can add using sum-ifs formula.
I am using drop-down lists in a cell to filter out some data in a spreadsheet. Works great, but I would like to hide certain columns depending on which item is selected in the drop-down list. I have written a macro to do this, but I cannot get the macro to run automatically every time there is a change in the drop-down list.
View 9 Replies View RelatedI am looking for a macro that will (most likely via button) create new table under an already existing one.
So for simple example, I have this ....
I have calculated data (the column and row headings are calculated as well) similar to this for which I need to create an automatically updating chart.
******** language="JavaScript" ************************************************************************>Microsoft Excel - test.xlsm___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEF1 BalloonsFlagsCushionsPillowsStickers2Blue136013Red352104Green703245Yellow075056Orange558327White26073Sheet2
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The chart should update if the data is filtered; so if a user doesn't want to see the 'orange' row they can filter the data accordingly. There is a finite number of rows and columns in the range but sometimes there will be less data. I have tried to convert the data to a table and that works quite well except the legend keys persist: so if there is no data for pillows and stickers the legends still appear and have to be removed manually. Also, there seems to be a bug in Excel 2007 whereby the removal of a chart linked to table data causes a persistent error message to appear.
I know that I need to created a named range for the data series using OFFSET, and I have also read that I need to refresh this formula using VBA but I am now very confused and none of my attempts quite work.
I'm looking for some VB code that will enable me to dynamically create Textboxes for every value that I have in a range.
Example date in range B2:B5:
apple
banana
orange
pear
I would want the macro to create 4 Textboxes on the active worksheet, each one with the text contained in the range.
I am trying to create a table of contents for a very long list of items on a sheet. I am using shapes on a seperate sheet to act as buttons, that go to the location on the correct sheet using a hyperlink. I found how to add a hyperlink using a macro, but I can't find how to create a hyperlink for the shape using a macro.
What is the code I would need to set a hyperlink to "Sheet1!A2:Sheet1!C2", with the a tooltip of "Some text"?
Here is how I create the shapes:
With myDocument.Shapes.AddShape(msoShapeRoundedRectangle, 10, IntVerticalOffset, 630, IntHeight)
.Fill.ForeColor.RGB = RGB(204, 255, 204)
.Line.ForeColor.RGB = RGB(0, 0, 0)
.TextFrame.Characters.Text = StrRowName
.TextFrame.HorizontalAlignment = xlHAlignLeft
.TextFrame.Characters.Font.ColorIndex = xlAutomatic
.TextFrame.Characters.Font.FontStyle = "Bold"
.Locked = True
End With
I am trying to figure out a way to delete the zeroes using VBA codes .
There is a spreadsheeet that contains company code, account, amount1 and amount2. If both amount1 and amount2 are zero, the VBA code will delete the entire row automatically. I am thinking of the With Range feature, but how I would determine what is the lastrow of the spreadsheet? Also how I can find the first zero and then the second zero in the same row? I vaguely recalled the find function, but can't write out the codes.
I am trying to delete many rows in a spreadsheet with over 45000 records. There are 10 columns and column B is the determinant of whether a row should be deleted. The criteria is that if column B contains format like 'P00000xxxx, then it should be retained.
Otherwise, the whole row will be deleted. I started to record some codes but then was stuck with the deletion portion.
Below are the codes that I tried to use:
Dim rng As Range, PCrng As Range, PCstring As String
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Set rng = Range(Selection, Selection.End(xlDown)).Select
For Each PCrng In rng
If PCrng.Value " 'P00000xxxx ' I don't know how to search this format, the last 4 digits will vary from case to case.
then delete the row