Creating Tables In Spreadsheet Won't Let To Insert New Row?
Apr 17, 2014
sample worksheet Antligen.xls
I have a spreadsheet that I need to add new rows everytime I have a new client. I a formula in the N column and a formula in the F column that I would like to automatically insert itself into the new rows.
The problem is this: I have arranged my table so that I can look at clients alphabetically, and according to other date... BUT now it wont let me insert a new row when I have a new client!!!
Is there anyway of keeping it so that I can insert a new row into my table, but preserve all of the formulas that I want automatically put into both the F and N column?
Attached is the sample spreadsheet.
View 1 Replies
ADVERTISEMENT
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
Jan 10, 2014
My family owns a Fast Casual Middle Eastern/ Mediterranean Restaurant. I'm taking charge of making it as profitable as can be! Our plates are different combinations of kebabs, rice, Falafels, Gyros, salad, platters. So theirs tons of combinations.
I am Dead serious about knowing REAL plate costs:
I spent days entering price data, weighing meat, produce, calculating usable yields. I then built "component builders" using data validation drop-downs, and Index + Match functions to construct the costs of the recipe items,standard base items and side items. So I now know How much a skewer of Marinated Kebab Costs me, and all the Components that go into building a plate.
I've now built a "Plate Builder" (See Pic) to create the EVERY combination available at our restaurant. So when I'm done filling in all the builders, I'll have a whole lot of tables. I'd like to pull the name and plate cost from each table and create a big table organized by whether its eat in or take out. How do I do it.
TL;DR: Need to create a table that pulls data from several other tables....
View 2 Replies
View Related
Apr 1, 2009
I am having a lot of trouble with pivot tables in Excel 2007 VBA. I am trying to create pivot tables using macros (connected to buttons the user can press to create the pivot table) - please don't ask why, but i need to do this!!!
I used the record fuction in excel 2007 to produce macro code which will produce the required pivot tables when the user presses a button.
Unfortuanately the coding seems to work fine when i have one pivot table in a file but breaks down if i record code to produce another pivot table.
I have attached some code below (which was produced by the record function) and is intended to produce 2 seperate pivot tables (the macro submacro2 produces the 1st pivot table and the macro submacro4 lower down the page produces the 2nd pivot table). I have also indicated the point in sub 4 where the code breaks down - basically submacro4 just doesnt run!
View 14 Replies
View Related
Aug 12, 2006
I've attached what I've done so far. I'm trying to figure out a way to capture everything. For instance, instead of using separate boxes for the different groups, isn't there a way that I can put the # Registered, # Attendance, % Attendance, % Didn't Attend, Units Didn't Attend, and list the different units, and different groups. I'd like it to be sort of like the very last box where I have the different sessions and the different groups in there, but I'm not sure where I could add the # Registered, # Attendance, % Attendance, % Didn't Attend, Units Didn't Attend, because the last box only formulates the percent attendance for each session by the different groups. I need it to also formulate the # Registered, # Attendance, % Attendance, % Didn't Attend, Units Didn't Attend. Is there a way this can be done without using separate boxes for each group.
View 3 Replies
View Related
Jan 18, 2014
I have a spreadsheet with two tables in it. Table 1 contains some data consisting of two columns both populated with data (where 1 = a, 2 = b, 3 = c and so on) and table 2 consists of two columns one column contains data and initially (numbers) column two is blank. Now I need a VLOOKUP that will look at the second column (red letters in the example) in table one, and where the same value (number) appears in table 2 populate the corresponding letter.
I do not wish to use filters as the table is extremely large. I have attached an example
View 4 Replies
View Related
Jun 24, 2014
Sync these two tables? the firs one is connected to a master sheet so when that master sheet is updated by entereing data, inserting rows or deleting them so is the table, the second table was created to hardcode data manually so it doesn't feed from the master. My problem is that if a row is inserted in the first table, the second table doesn't update that change. Is there any way to connect both?
View 2 Replies
View Related
Mar 22, 2013
how to do this, but I know it can be done.... I want to use one drop down list created via the Data Validation 'List' Criteria to then lookup data based on the selection made in this list...... this will require a number of reiterations to get to the final result......
So:
Drop Down No:
1. List: Department Function:
2. List: Sub Function List Based on Selection from 1.
3. List: Job Code, Title and Pay Grade based on Selection from step 2
how to ensure that we are able to minimise the overall workbook size due to the complexity that is required here as this is just the basics.... this will need to applicable further once this basic requirement has been fulfilled.
View 9 Replies
View Related
Sep 17, 2011
I'm working on a national survey with valuable data from students. The plan is to turn around, 100 different reports to the departments with their respective students.
The issue that I have is creating multiple tables from multiple sheets in excel into a word table, any way of automating it.
What the tables look like in excel:
What I want them to look like in word, with a chart if possible:
I realize that I can just copy and paste easy table into word from excel and with a few clicks of a button, get what I want. But i'm looking for something to automate and simplify this process. There are about 300-400 variables that I need to crosstab with certain demographics.
If it matters any, I bought a software package (Q Market Research) but it doesn't make the tables look exactly how I want them to look. The data is from SPSS, those tables are flat out.
View 9 Replies
View Related
Oct 29, 2013
I am trying to solve in VBA how to create pivot tables in new sheets using objects instead of relying on the sheetcounter, which errors out.
I found an old Tip on Mr. Excel: Excel Create and name a new worksheet with VBA
But I can't figure out how to apply the object to the rest of the script. Here is what I have so far:
Sub Macro2()
'
' Macro2 Macro
'
Dim WS As Worksheet
Set WS = Sheets.Add
'
[Code]....
View 1 Replies
View Related
Apr 18, 2014
I am trying to insert via a command button a new row in 2 different tables but I am finding some difficulties . I have attached an excel sheet as example.
View 3 Replies
View Related
Jun 28, 2013
I don't have Excel 2013 so unfortunately I can't easily create relational data in Excel 2010. I'm looking for a solution to a design problem. I'd like to have 2 tables which I can join with Microsoft Query and run pivot table reports.
My department processes payments, both for internal clients and external clients. My Payments table looks like:
Date | Type (internal/external) | Operation (what type of payment) | Method (internet, mail, etc) | Quantity
Additionally, I have a table for Mail Opening, which looks like:
Date | Employee | Operation | Quantity
My overlapping fields are Date and Operation. Using each table individually, I can get nice pivot table reports. What I'd like to do though is be able to not just see what operations and methods were run each day with what quantities, but also to compare that to how much mail was opened. Employee and Operation is a multi-multi relationship, so when I join by date, I end up getting incorrect numbers because of problems with the data layout. I'm open to changing my data structure, as I know the way it's set up right now isn't great, but I'm having a mental block on how to redesign it. I attached a sample workbook.
View 3 Replies
View Related
Jun 28, 2012
I have created the code below to create a Pivot Table using VBA, so that it populates from a list box option.
I would prefer if it didn't show any subtotals and was in a tabular form. It keeps dropping off at that point for each row field.
CODE
Sub trail()
'
' trail Macro
'
'
Dim wksPivot As Worksheet
Dim wksData As Worksheet
Dim pc As PivotCache
Dim PT As PivotTable
Set wksPivot = Sheets("PIVOT")
[Code] ......
View 2 Replies
View Related
Feb 4, 2009
I have 52 tables in one spreadsheet to represent 52 weeks and have created another table to sum the weekly data but the sum function doesn't do it because of the amount of cells which need adding.
example: c3, c14, c24, c34, c44, c54 etc...
View 9 Replies
View Related
Dec 12, 2013
I need to insert a blank row between each row of data under my headers (not the immediate row) until I reach the end of the particular table. I then move down to the next header and repeat the process.
My starting data looks like this:
HEADER
DATAROW1
DATAROW2
DATAROW3
-emptyrow-
HEADER
DATAROW1
DATAROW2
DATAROW3
I need it to look like this:
HEADER
DATAROW1
-emptyrow-
DATAROW2
-emptyrow-
DATAROW3
-emptyrow-
[Code] .....
View 3 Replies
View Related
Jan 3, 2009
I need some help, I know very little about macros but is it possible to create folders/directories to a certain path using the text in an excel spreadsheet cell? For example, if cell a2 has a street address and cell b2 has a city, can it automatically create a directory in the path of my choice using that data? If yes, can it go down the the rows and create directories until it is done?
Please help as I have hundreds of directories to create.
View 6 Replies
View Related
Feb 21, 2010
I am trying to create CSV files from Excel spreadsheet for each tab. But when I convert these CSV files, text in cell is limiting to 255 characters, in otherwords it is getting truncated after 255characters. I am using Excel 2003 version.
And also when convereted these windows CSV files to UNIX csv files using the command "DOS2UNIX", some charaters becoming special characters. For exmaple, I have the actual text as "If AEENDTC ne '' then do; " and is becoming "If AEENDTC ne æÆ then do; " Do you have any idea why it is doing this.
View 14 Replies
View Related
Apr 5, 2013
I think I'm just missing something obvious here but I want to calculate commission based on £1 Plus 10% of Hammer Price. That bit's easy but I need it to ignore the hammer price if blank. In other words if I leave the hammer price empty, at the moment the sheet adds £1 to the commission cell.
In Summary:-
F3 is Hammer Price
K3 is Commission (=(F3*.1)+1)
K3 should NOT state £1 if F3 is empty
View 6 Replies
View Related
Sep 17, 2013
Currently I'm working on functionality in my staff holiday spreadsheet and I've come up with this amazing idea for a calendar overview for month by month.Now the sheets I currently have is summary and jan to dec.
I plan on adding the sheets Jan (Cal) to Dec (Cal) now this is when my idea gets nifty - The calendar view will import the information from the month table and will show it on the calendar.
For example X has 1 holiday in July for the 20th - this will show up on the calendar as "x on holiday", I'm still working with spreadsheets so I'm not sure if excel is powerful enough to do this - but is this actually possible?
View 2 Replies
View Related
Nov 5, 2013
Is there a way to make a simple text analyser that will return the number of times each word is used in a sentence or paragraph within a cell?
I use excel to write short paragraphs to upload onto social network sites and I would like a formula or program that tells me how many times each word has been used over a number of cells, to avoid repetition. Is there a way to do this?
View 3 Replies
View Related
Dec 23, 2010
I have set up a spreadsheet but need to add a tick box, I just need to tick it once the item has been completed.
View 6 Replies
View Related
Apr 14, 2008
I need to create a macro that will move information from a range of columns and then insert them under existing information in another column.
here's my example, this is what I'm starting with:
There are only 3 rows in this worksheet and I have numbered the columns just for reference in solving this problem: Anyway, this is how it looks when I import the records: ...
View 16 Replies
View Related
Aug 2, 2012
I am a personal loans officer and have created a worksheet to input a range of information about an application. The information is personal details and details about the loan. The data I input runs down Column D [with line breaks to seperate the answers into categories] with the question beside it in Column C.
I do this for every new application as I use it for reporting on my loans as well as mail merge to produce documents for the loan.
Currently I am doing one per loan but would like to have a button at the bottom of the page that once clicked, it will push the data onto another worksheet so that all my loans for that month are on the one spreadsheet. The data will be pushed onto a row, not a column, but it will also need to find the next available row to put the data on.
Once the button is clicked, the first worksheet will clear ready for the next loan to input and the data will transfer to the 2nd spreadsheet. And, if possible, on the first sheet will be a drop down list of all the current loans so I can recall any application details to the first sheet if needed to update details. So if the 'loan number inputed' already exists, it wont create a new line on the 2nd spreadsheet, but will know just to update the fields.
View 7 Replies
View Related
Feb 9, 2014
create an excel spreadsheet, where i can enter all my products name, unit price etc. so when i am doing an invoice for the customer, if i just type the product name price will show up automatically.
View 4 Replies
View Related
Dec 12, 2011
In MS Excel 2011 for Macintosh can I assign a macro to an object, such that if the user clicks the object it runs the macro? That is creating a button on the spreadsheet that will execute a macro.
View 2 Replies
View Related
Nov 11, 2013
I am creating a spread sheet with a large amount of data.
In column a I have a list of managers, in B I have a list of employees and C, I have a list of activities that have been recorded throughout the day.
What I want to do is create a list (on a new sheet) so in column A that would bring back all the managers. Then in column B, bring back the employee that is tied to the manager from column A and finally in column C, bring back the activity that is tied to the employee.
I know how I can do this by doing list validation with INDIRECT, but with the large amount of data that I have this would take forever in a day.
Is there an easier and quicker way to do this?
View 1 Replies
View Related
Mar 16, 2007
I have two spreadsheets. I need to be able to match information from detailed spreadsheet for specific information from a lookup spreadsheet.
The detailed sheet (call it Purchases) has information about what was purchased during a month.
Columns: ...
View 9 Replies
View Related
Jun 9, 2014
sample pic.png
I have two spreadsheets of data I export regularly from an outside source. I want to consolidate the data from these two sheets into one large sheet with all the data in a more concise form bringing in only the data related to certain parts I am conducting analysis on. I have a list of these parts already. My first thought on how to do this would be to use the list in an entry for an input box and have that list be the index for a for loop in which i searched for a part code, copied the entire corresponding row, and pasted it back into the master sheet. I have gone through and tried to do this but I am running into problems because sometimes I have multiple rows of data that correspond to a single part number. I have attached a template of the way that the data should look in the master file with the multiple rows of details that correspond to just one part entry.
View 1 Replies
View Related
Jul 11, 2013
Here's the spreadsheet I am talking about : [URL]
So as you can see each row = one day divided in 2 cycles of 12 hours : every 12 hours the cat gets a new injection of insulin (the columns "U" being the number of units of insulin the cat receives)
Now I want to turn this sheet into two different curves that would look like these : [URL]
As you can see the first graph shows data based on days (x) and insulin dose (y), while the second curves shows data based on days (x), and blood glucose values (y). This is basically the only way we could clearly see how the cat's disease is evolving over a long period of time.
If he had just started to take all these values from his cat I guess it would be simple to do, but since he already has a lot of data entered in his spreadsheet, I was wondering if I could use this data (and not write each number in a new document), to make things a little more simple and to avoid mistakes.
View 9 Replies
View Related
Nov 3, 2013
I want to create a spreadsheet for figuring the cost of some of my favorite recipes. Right now I have a table of basic ingredients (milk, sugar, etc) along with cost of same.
What I want to do is be able to set up a recipe that calls for "3 eggs, 1 sugar, 1 milk" and automatically figure the cost for me.
Ideally, I'd like this to be a "building block" spreadsheet - where I can have a recipe for "roux" (1 flour, 1 butter), and then a recipe for "broth" (1 chicken, 3 water), and then a recipe for "gravy" (1 roux, 2 broth) - where the spreadsheet is smart enough to do the math for me - and if the price of chicken goes up, I can change that in my basic list and it will carry down. And obviously, I want to be able to add ingredients and recipes easily.
View 3 Replies
View Related
