I have these formulas auto-filled to the bottom of the sheet of each column. The problem I'm having is that with this setup, the return on the G column is giving me
#VALUE!
for all rows that do not have any values entered yet. Is there any way to fix the formula in column G so that it reads the value of the cell instead of the formula in the targeted cell?
I need to display a set of cells based on the value of two drop down cells i have. As I am not very good at english and worse at explinations, I'll try via screen shots...
I have two dropdowns (C4 and C6) that will indicate what table to use (Second sheet / screenshot). I want that "table" to display in the yellow box on the first page. To complicate matters, some options do not have a CLA option - those starting with X. As there are 24 different outcomes and each is 3x9 if/then statements just dont seem to cut it.
I am trying to enhance our estimating spreadsheet. If you look at the example attached it is a snippet of the spreadsheet. What I am trying to do is make column AJ (balanced bid unit) include subordinate items. The way a sub item will be defined is column B (bid item) if column B = S, then its balanced bid unit need to be reflected in the column AJ. I would also any item titled as Y in column B (bid item) to include all listings as S (until next item as Y) in the column AJ totals.
For the small database in my example workbook, I would like to apply credits earned at a later date to the oldest charges and create a "To Date" balance. After creating the "To Date" balance, I would like to select the smallest "To Date" balance as a way to identify the first charge that has no payment. Please see the attached for more clarity and additional information. The last column shows the desired results.
The situation I have on my hands is over 200 users, were the majority can't use Excel's built in function to create PDF's. Yes, I've tried to teach them, but I'm probably not a good teacher.
So, I've resorted to create a button at the top of the worksheet, that says "Create PDF". I thought this was gonna be very easy to program, but alas.
Is there a way to start the built-in Excel dialog that happens when you click "Create PDF/XPS" in "Save & Send"?
I want to make a bar chart, When i use Excel and try to make it - i get a chart which shows the right column height but the Male and Female bars are BOTH IN 1 Colour. How do i make it so that male bar is blue and female is pink.
I am not too sure what this is called but I am using Excel 2010 and need particular document that I use repeatedly. Basically, if a particular cell in the document does not contain a phrase then I want Excel to automatically change the text in that cell to bold and red and 2 other cells in Excel to bold and red as well. For more information, the cell is 19–20 and K–O and the phrase in the cell is "No Inconsistency".
So if "No Inconsistency" is not found in that cell then it automatically changes the text in that cell to bold and red, and it also changes two other cells that are found in that document to bold and red as well. The coordinates of those two other cells are 19 with H–J, and the other one is 17 with H.
Im looking to create a bar chart in Excel 2010 where the totalnumber of widgets is 33 and the number produced so far is 11. The bar chart would be a single bar that represents the percentage complete (33%) with the chart maximum range being 100% which would represent the number 38.
Ive included a mock up example of what the data and chart would look like
I'm trying to create a userform with a progress bar. The progress bar does NOT need to be 'real' and accurate, but simply act as a timer to illustrate to the user that the program is running some quick calcs.. Again, basic progress bar, with about about a 4 second completion time..
Code: Date Jan-14..........Jun-14............Dec-14...........Jan-15..........Jun-15............Dec-15 Measure1 Measure2 Measure3
Code: Date 2014 2015 2016 Measure1 Measure2 Measure3
I have two tables, examples above In the first table, Jan-14 is a dropdown value that updates all the values to the right by a month increment when a specific date is selected
I then have some code that populates the cells which have 2014,2015,2016 in them.
What I need to do next is populate the 2nd table with values based on the date ranges in the above table.
I'm trying to create a pivot in vba. I can record what I have done but if I run that recorded code, it returns an error "Invalid call procedure or argument".
I have a workbook which needs a new sheet for each day of the month.I normally just create and re-name each sheet with the date...is there a simpler way where i can create the new sheet with a date for each month instead of doing indiviually?
I have 6 columns. The 5th and 6th columns are the most important. The 5th contains a figure and the 6th contains a type.
Bascially I am trying to do a basic chart showing the following 2 things. A. Do a percentage chart of column 6 (service) but not of percentage of hoem many lines the product is in. Not based on percentage of how many times Apples are shown compared to other products. I need it to add up everything, then do a percentage of total amounts of oranges and total of for Apples from the figures on column 5. Also, if possible, the pie chart will even show teh actual total figure per product in each (or next to) each pie chart slice.
know the easiest way to do this (step by step) in Excel 2010 and then I can perhaps write a macro for the future?
I need to create 3 or 4 detailed reports from an excel 2010 worksheet.
My worksheet contains data including date, invoice number, company , consultant, days, dollars. There are approximately 100 records in the worksheet.
Requirement 1 :I want to use excel 2010 to automatically generate a series of reports broken down by consultant showing date, invoice number, company, and consultant with totals for days and dollars, when all the records for consultant 1 are listed, then I want to show consultant 2.
Same for any other consultants with a grand total at the end.
My understanding is that excel 2010 makes this process very simple .
I am trying to create a database that contains data taken from several work books that i have saved in one folder on my hard drive. These workbooks contain quite a lot ** data, but i only want to extract the data i want to instead ** having to extract it all? these works books are used by several other people as well, so i want it to be able to update as data changes.
I was thinking about using data links, but that only seems to extract all the data instead ** just what i want?
As you can see in the attached file, I have a column of classes , a columns of related revenues and the total for each class. I have to create a column, next to these, where I can create the percentage with respect the sum of each class (as you can see by the formula). My problem is I am not able to say vba to find the totals and to create the percentage just with the revenues related to that total.
I get a "Object variable not set" error when creating a Pivot Table from a cache. I want the destination to be in a different sheet in the workbook. I set the new worksheet as: Set WSD = Worksheets.Add and I reference WSD.Cells(2,FinalCol +2) in the destination field of the CreatePivotTable method. I use the code from the Excel 2010 VBA book I got last week.
I am working on a schedule worksheet, the days are the columns and the staff member names go down the rows. I want to create a new cell value based on a condition on each row. I have been successful working with one row but how to efficiently do the same for each row.? I'm working with Excel 2010.
I want to create an event log by which I am able to track changes by the users. we work on LAN and any body can write on the workbook. my work is to check for discripencies. If I get any type of help by which I can track the user name, date, cell that has been changed and the PC address. I got this code from ozgrid. in place of "c: racker.text", I used the path where to place the event and I was successful.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Locked Then TrackFile = "C:Tracker.txt" TargUser = Application.UserName TargAddr = Sh.Name & "!" & Target.Address(False, False) TargVal = Target.Resize(1, 1).Text TargDate = Format(Now, "yyyy/mm/dd hh:mm") x = TargDate & vbTab & TargUser & vbTab & TargAddr & vbTab & TargVal Open TrackFile For Append As #1 Print #1, x Close #1 End If End Sub
I want to get the PC address from where the changes were made.
Using Excel 2010, I am trying to create a macro to insert INDEX and MATCH formulas on a sheet that reference Tables on the same sheet.
The formula works fine when entered in a cell where I can point to the Table. Now I need to put the formula in a macro where it will automatically reference the first Table on the sheet.
The original file has multiple sheets and multiple Tables on each sheet. No naming convension was used in naming the sheets or Tables.
Can I cycle through all the Tables on a sheet and rename each of them? For example, find the first Table on the sheet closest to cell A1 and rename it Table 1, find the next Table and rename it Table 2, etc.
Is there a way to reference Tables based on an index (similar to sheets)? Is there a Table(1) and Table(2) referencing system? Is it on a per sheet basis or across the entire workbook? That is, can there be more than one Table(1) in a file?
Can I start in cell A1 and find the first instance of a Table and return the name or index of that Table?
There was an option in excel 2007 in the pivot wizard to use an existing pivot table report as the source for a new pivot. I cannot find this in Excel 2010.
method. It seems like the limitation is in the Countif function going over 1000 (or some other size limit)
I have a list of ~1500 rows, of that there are approximately 55 unique items. Doing the unique array works correctly up until item ~40, upon which it fails by returning the 1st item only (for the rest).
Trying to debug, and pulling out the Match section, it functions up until 976 (that is
MATCH(0,COUNTIF($N$1:$N40,$C$3:$C$1500),0)
returns 976) anything after returns just 1 (1st item).
It seems to be a limitation on the text string size that COUNTIF can handle.
I am trying to make a tally sheet to keep track of my inventory of inserts. I am trying to make it as user friendly as possible as my operators do not have much experience working with computers. I will attach what I have made so far. The only math functions so far are: C4=B15-C15.
Right now the operator has to look at C4 to see current total, type that number into B15, then in C15 type the number of inserts thrown out to show a new current inventory total in C4. Is there a way I can set this up so that all an operator has to do is type in only the number of inserts thrown out in C15 to give current total in C4. Is there a way to make B15 know what is in C4 without the operator having to type it in. If so is there also a way to make C15 the only cell that can be edited.
