Forcing Tables To Match In Size
Mar 14, 2013
I am working on a project from work and am having trouble with formatting the final version that is sent to customer.
We have one tab that is named "initial" where all of the information such as Serial number, connection type, etc. is entered.
We have another tab, named "final", that is automatically populated with this information. This final tab is locked from the end user.
Once the user finishes entering data into the "initial" tab they delete the rows below that are not used. Because these rows are now deleted the "final" sheet shows "#REF!" for all of these cells.
I have attached some screen shots as examples.
I would like to be able to put some logic in the formula to make the output on the "final" tab match the initial tab. Ideally, when the user deletes the rows that are not used on the "initial" tabs this controls the amount of rows seen on the "final" tab and we don't have all of the #REF! symbols.
View 6 Replies
ADVERTISEMENT
Mar 20, 2014
I currently have a problem with suming the costs from tables across multiple sheets where the tables are of varying size.
Each sheet, 'sheet1' to 'sheet10' for simplicity sake, has a simple table consisting of two columns, 'Item' and 'Cost'. Each row contains a certain item and it's respective cost. The bottom of each table then has 'Total' in the item column and the sum of the total costs in the 'Cost' column.
However, each table on each sheet has a varying number of rows. This means on 'sheet1' the total may be in B5 and on 'sheet2' it might be in B7.
I need to sum all the total costs from 'sheet1' to 'sheet10' in a single cell.
I tried using the =SUMIFS formula with a 3D Range but I discovered that you can't use this formula with a 3D Range.
The only solution I could think of is to use a loop that goes through each sheet in turn, searches Column A for 'Total' and then adds the cell next to it to a running total.
View 3 Replies
View Related
Jan 9, 2013
I'm trying to make a macro that match table in sheet "Vocabulary" to the second table (columns Q,R,S,T) in sheet "Overview" and compute correct values in column U using weights from column F, sheet Vocabulary on values from a corresponding cell at column M, sheet Overview. The entries for every ISIN in the sheet Vocabulary are in no particular order and some positions from sheet Overview don't have to exist in Vocabulary at all (but I still need them to display in the table on the right to be able to make charts from summing these categories).
Portfolios.zip
How should the end result look like is displayed on the last sheet.
View 1 Replies
View Related
Dec 21, 2007
My goal is to match two outputs from a database so that the same person's record can be found on one row. At present output 2 does not marry up to output 1 due to staff leaving and new appointments being made. The data in output 1 should remain static. The data in output 2 must be manipulated to match the order of output 1.
Output 1 is a 5 column record containing (in A6:E6)
Forename, Surname, Branch, Roll Number, and a % score
Output 2 is again a 5 column record containing (in G6:K6)
Forename, Surname, Branch, Roll Number, and a % score
Each employee can be identified by a unique Roll Number Code contained in the fourth column of each record.
If an ideal match has been found I'd like the letter Y to be inserted in the L column of each record to signify a positive comparison. All non matches should have the letter N in the L column & these non matches should be placed at the bottom of the dataset.
View 2 Replies
View Related
Jun 7, 2007
Referring to the post Find Min Value In Multiple Columns And Match The Header. I would like to go little bit further and see is there a way to find and match not just in multiple columns but in multiple tables (see the example)
View 4 Replies
View Related
Jul 1, 2013
Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then
[Code].....
View 2 Replies
View Related
Jul 16, 2009
I have the following formula:
View 2 Replies
View Related
Feb 26, 2009
Command button opens userform with a listbox. Unfortunately during testing I found that if a value is not selected from the listbox and the OK button is clicked, it bugs out.
Is there a way to force a cell value from the listbox? BTW, the listbox first number is 3 so it would be my choice of default.
I would imagine the code would need to be with the OK button, so here is the code for it:
View 7 Replies
View Related
Jul 30, 2008
I'm importing data from CMS into excel and I then have several other sheets that harvest the raw data from the import and use it to calculate various things.
My problem is that when I import time data, if it is less than 1 hour (eg 39mins, 12 secs) it will format as :39:12 rather than 00:39:12 and for some reason the calculations in other sheets omit any data that does not contain the hours field.
I have tried formatting the cells as hh:mm:ss but this does not force inclusion of the hours field.
I guess what I'm asking is:
1. Is there a way of making excel include both data in the hh:mm:ss format and :mm:ss format in the calculation?
or
2. Is there a way of forcing data in the :mm:ss format to include an hours field populated by zeros?
View 7 Replies
View Related
Dec 6, 2013
I want to have a fullscreen application all the time, so for this I've added to things in my code:
1) Application.DisplayFullScreen = True in Workbook_Open
2) Same in WorkBook_WindowsResize
I open my workbook, it goes fullscreen. Until now all OK. However, if I press ESC or doubleclick on the topbar, it goes out of it.
Now the weird thing: if I click the "Restore window" button (which is right under the application maximize button that incidentally I have disabled) it snaps on fullscreen and THEN it keeps this state forever, even on ESC/doubleclick. Which is what I want.
So my question is: what does exiting from fullscreen and going back in change so that the workbook event starts triggering, and how I can replicate it programmatically? I've already tried WindowState = xlNormal followed by xlMaximize, it doesn't change anything.
View 3 Replies
View Related
Oct 21, 2008
recently excel 2003 began forcing the save as dialogue box when i try to close the workbook. I do not remember installing anything in particular on my machine when it started acting up.
I tried uninstalling office with Revo Uninstaller (gets rid of those pesky registry entries that the control panel add/remove leaves behind).
I reinstalled and I have the same issue.
Im thinking it is a macro of some sort as when I open the program holding down shift, it does not prompt me to save as when I exit. I do have one excel add in installed, but I have used it for many years without any issues.
View 9 Replies
View Related
Mar 16, 2009
I have created a scorecard and I have certain fields which i need completed prior to the coach submitting the scorecard.
Is there a way which i can prompt the coach if they miss a field
The fields i want completed are:
X17, Y17, Z17, AA17, AB17, AC17, AD17 and AF17
View 9 Replies
View Related
Aug 1, 2006
I do not write code in it, just use it for school and work on the most basic levels. I am trying to suck an Excel document into SPSS ( Statistical Package for Social Science) for my Engineering internship. I am logging temperature using Omega Engineering, Inc temperature probes. When converting the data recorded on the probe to excel, it does something funny. I will try my best to explain what it is doing. If you format the cell to general, it gives you this (38917). Then simply changing the format to date I get this (7/19). How do I change this to make it so the date is the only memory in the cell. I don't need the other number in there because when I suck it into SPSS it only sees the 38917 number and not the date. I need the date to do my statistical analysis on the data.
View 4 Replies
View Related
Jul 25, 2013
Let's say my horizontal scrollbar is 2/3 of the way to the right and I'm currently viewing columns EA thru EZ. If I turn on VBA macro recording, the Excel auto-generated VBA macro code that gets saved when I press Ctrl-Home is Range("xx").Select, where "xx" upper-left-most cell based on frozen panes [e.g. Range("T3").Select]. While recording the macro, when I press Ctrl-Home my Excel view horizontally scrolls all the way to the left such that cell T3 is selected such that I'm now viewing columns T thru AS (which is what I want). If I start with the horizontal scrollbar 2/3 of the way to the right again and I use Range("T3").Select in a VBA macro, cell T3 gets selected like before -- but Excel doesn't horizontally scroll all the way to the left to where I would be viewing columns T thru AS; it just stays with columns EA thru EZ in view. Is there some way to force the horizontal scrollbar all the way to the left with VBA code? I tried searching the FAQ and didn't find anything there. I'm using Excel 2007 on a Windows7 PC.
View 2 Replies
View Related
Oct 20, 2008
I am using the below code to force a range of cells to change to Upper Case, what i am after is an exception to the rule for example the words Pick Up i don't want to force to upper case, is there VBA i can add to the below to achieve this?
Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''
'Forces text to UPPER
''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("w6:w299")) Is Nothing Then
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End If
On Error GoTo 0
End Sub
View 9 Replies
View Related
Dec 3, 2013
the way my spreadsheet's set up so far is that each employee should have a goal of, say, 100 for how many people they need to sign up. But they each have specific geographic breakdowns in their turf, some of which might be bigger or smaller than others, but at the end should all add up to 100 for each employee.
It's not super hard to do since I just take a goal of 100, in this example, and multiply it by the percent of population of the area in their overall assignment and give them a goal for that area based on that. So if Philadelphia has 70% of their total population, Upper Darby has 20% of their total population, and Phoenixville has 10% of their total population -- the goals would come out like this:
Philadelphia, Goal: 70
Upper Darby, Goal: 20
Phoenixville, Goal: 10
Ryan, Total Goal: 100
For some of the employees, that works out perfectly -- but based on the population sizes, the rounding sometimes gets a little off and it could end up like this:
Philadelphia, Goal: 71
Upper Darby, Goal: 19
Phoenixville, Goal: 11
Ryan, Total Goal: 101
For most employees, it ends up being dead-on 100 -- but there are some whose totals are at 98 or 99 or 101, and I was wondering if there was a way to force it to shave a point off or add a point on somewhere, pending on how close the decimal was when it rounded to make sure that it always ends up exactly on 100. (I'm using 'Data >> Subtotals' to get what's being represented above as "Ryan, Total Goal.")
View 10 Replies
View Related
Jan 10, 2010
In a multi sheet workbook (Excel 2007), could some one tell me how to force sheet 'Main' to be displayed?
View 2 Replies
View Related
Mar 7, 2012
Is there Code for forcing all text in a certain range to Uppercase?
Example: A1:E20
if possible I need the code to be just inserted into the sheet code. Maybe were when the user moves to another cell it changes them then.
View 2 Replies
View Related
Nov 28, 2008
i would like to put a command button on one Excel sheet that will force the formulas in another Sheet within the same file to be recalculated. I know how to put a command button, and i know some VBA. So can anyone give me a hint how this might work?
View 9 Replies
View Related
Apr 8, 2009
Is it possible to lock a cell so that the user must enter the number manually as opposed to using a formula.
There is a cell on one of our spreadsheets that the user should be hand entering the numbers from the general ledger this is done to ensure that the general ledger balance is the same as the spreadsheets totals. However most of the user insert a formula that just copies the number from above into this cell that should be hand entered.
View 9 Replies
View Related
Apr 26, 2007
I have written a custom function which is called in lots of cells. It had an error so I modified it. But the modification did not automatically trigger recalculation in the cells where it is used. Neither did F9 (manual recalculation). The only thing that did is hitting F2 for the cell then ENTER. But what a pain to do that for every cell it's used.
View 5 Replies
View Related
Jun 4, 2008
I am wondering if there is any way to "force" a number to have three decimal places instead of two and still have that number valued as a number for use in formulas later (like AVERAGE, etc.).
What I have is a combination of HLOOKUPs, IF statements, and Conditional Formatting.
I have a Master Grades sheet and individual Class sheets. I use an HLOOKUP, matching a Student Number in both sheets, and looking in the Class sheet for the P/F indicator. If the indicator is a F, the HLOOKUP is to return the Final Grade from the Class sheet and input that into the Master Grades sheet, forced to three decimal places (whereas the numbers in the Class sheets are at two decimal places) If the indicator is P, the HLOOKUP simply returns the Final Grade from the Class sheet and inputs it into the Master Grades sheet with two decimal places.
I have the failure grades forced to three decimal places so that the Conditional Formatting in the Master Grades sheet highlights the failed grades for teachers and anyone else can see right away that a student failed, but the number still allows the Master Grades sheet to calculate the overall AVERAGE for the student as well as the AVERAGE for that class.
If any of this is not quite clear, please ask and I can further expand on what I am doing.
Here is the code I have right now that is not working correctly (and I know why it's not): ....
View 9 Replies
View Related
Nov 25, 2009
I have a advanced filter that works pretty much close to how I want it to. However I would like it to either cancel the new filter or copy everything if it finds 0 matches.
If the copied location is blank it breaks my sheet. So I need to find a way for it to never be blank, either by canceling it, copying everything, or finding some other way I haven't thought of.
Also for some reason my Advanced Filter does NOT Ignore blank "OR" cells. If I place a word in the top cell, then leave the bottom blank, it searches for the top cell or anything and I end up with everything. Its quite frustrating.
View 3 Replies
View Related
Apr 10, 2007
I want to force my user to input a date in a userform. I have a normal Textbox and assumed i'd be able to set the properties to mean that imput had to be an integer and the format had to be "00/00/00" but I can't see how to do this.
View 9 Replies
View Related
Mar 8, 2013
I need to have all of my number be "x10^-6", but Excel wants to make them, for instance, 7.66x10^-3, when I need it to read "7660x10^-6". How can I force excel to do this?
View 3 Replies
View Related
Feb 1, 2010
I'm working on a project for my company. We make plastic tanks and for quality control we want to start recording the thickness of the tanks in different areas/zones of each tank.
Attached to this message is an Excel sheet that I've been working on. From "Sheet 1", it records inputted thicknesses into WorkSheet "1098". On the top of "1098", it shows all of the recordings, and just below that are the "10 Most Recent Entries".
Right below the "10 Most Recent Entries", there are formulas to calculate the Min and Max Values. Whenever a new entry is recorded, the selected cells for the Min and Max formulas change. Is there a way to force the cells to always stay the same?
View 3 Replies
View Related
Jun 10, 2013
I use a certain portion of our workbooks to enter the due dates of certain equipment, which are always listed in month/year (e.g. 4/17), and which means that they are good until the last day of said month. That is, a due date of "5/16" is good through May 31, 2016.
I'd like for my users to be able to enter "5/16" and have that cell properly identify as May, 2016 (instead of May 16 of the current year)...more specifically, the last day of May, 2016. All of this so that I can conditionally format any cell where that date has past - that is, if my user enters "5/16" on May 15, 2016, it won't flag.
View 8 Replies
View Related
Nov 5, 2008
I have the code below working as I want it to, when data is input in column 2 the date and time is automatically input into the corresponding cell 3 columns to the right. I want to ensure that the date is changed to the UK format dd-mmm-yy. I have formatted the column to be in that format but for some reason it has suddenly started changing the date to US format. Can this code be changed to force the date to be in UK format?
View 3 Replies
View Related
Mar 11, 2014
I am having some trouble getting a formula to work. I am building a report that pulls figures from a pivot table in another workbook. I am using a vlookup with match function to get the column index to find the relevant data I want. Where I need to add two columns together I am using sum, with the vlookup & match formulas nested in them e.g.:
=SUM(VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("FAID",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("BPCM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMD",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE))
Where:
F13 = Employee number
Column C on the pivot 156 workbook is where the employee number is based.
The Match formula is then getting the column index from the column headings of the pivot table ie. "FAID"
This in itself works fine, as long as it finds a match in the column headings. This is where i get the error as in the above function "COMD" is not in the pivot table. However I need to keep it included as it may appear on a future pivot table. Is there a way of getting the sum function to complete even though later in the formula it can't complete the vlookup? So it will ignore it, or assume the value is zero if it can't find it? The formula probably needs to do this for all the vlookups as some headings may drop off in future pivot tables.
View 2 Replies
View Related
Oct 8, 2009
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
View 2 Replies
View Related
