I'm working on a spreadsheet which will sum values from two source worksheets based on the month and year in certain columns. I put together a summary worksheet and was able to successfully pull the data I wanted. So my next step was to convert the Summary data into a table (because I prefer the aesthetic value of the "greenbar" effect in Excel tables). But as soon as I converted the cells to a table, the formulas returned nothing.
After playing around with it, I determined that the problem seems to be the fact that I use the year criteria as a cell reference (that way, I can later change the year in one cell and update all the dependent formula). I've got "2013" in Cell A1 in General format, and all the formulas use a YEAR=$A$1 criterion. I found out that if I changed the formulas to YEAR=2013 instead of using the cell reference, I got the desired result again. But I cannot quite understand why changing the data into a table would alter the way Excel interprets the formula (plus, I still want to be able to change the year by updating one single cell).
I've created a spreadsheet at work that has two summary tabs which contain hyperlinks to around 30 separate sheet tabs.
On each sheet tab there is a list of unique values in column A (and other information relating to each value in columns B to D which are repeated for more than one unique value). In column E, users enter a test script name against each unique value they wish to 'reserve', and the macro picks out the unique test script names and via the COUNTIF formula counts the frequency of each test script name for each of the different values in column B.
My problem is that the macro seems to work fine if the workbook is not shared, but errors if the workbook is saved as shared. The error is 'Run time error 1004 - Unable to select the MergeCells property of the Range class'.
Here is the macro code:
Sub Get_Policies_Per_Script(updCol As Long, ShtName As String) Dim rowctr As Long Dim tgtrow As Long
Const ppsformula As String = "=COUNTIFS($A$3:$A$65000,I$24,$E$3:$E$65000,$G"
If updCol = 5 Then 'test name column has been modified
I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.
I'm trying to get some formatting/formulas to work, but apparently I'm not doing it right (otherwise, I wouldn't be here ):
1. Column A has dates. I want Column A_ to be red if the date is a week old or more from today's date, and white if it is not.
2. Column A has dates, and column B has dollar values. I want three four summations: -Total dollar value from dates a week old from today's date -Total dollar value from dates 2 weeks old from today's dates -Total dollar value a month old from today's date -Total dollar value year to date
I have a niggling problem with a worksheet when using Hlookup to return a value from a range of cells it is coming up with blanks when there is data in the range.
There is only ever one row cell containing text in the specified range and i need this to be returned in another column.
The problem with the hlookup formula i am using is that it will not work if cells contain formulas and in the range i am working on all cells contain formulas.
I know this formula works if there is text without a formula in the cell range, as soon as it hits a cell with a formula though it won't work.
I have attached the sheet : Book4.xlsx
From j138 there is one cell with text i wish to be returned in column q for each row. E.g., in q1 the first cell text returned would be khawatim. q2 should contain cantor etc etc.
I have created two documents. One document (A) is my worksheet where I input data, the second document (B) is my spreadsheet where it comprises all my data into the fields I want. I have completed both documents on computer (1) and now need to transfer it to another computer (2). However my formulas no longer function after I move the document (A) from computer (1) to computer (2). Is there a way for me to work around this? Do I need to transfer the spreadsheet (B) to computer (2) in order for them to function correctly? How can I make it so I have the worksheet (A) on computer (1) enter data correctly into the spreadsheet (B) on computer (2)?
I've developed a spreadsheet that shows what errors people made in their spreadsheets. I compare cell A1 in spreadsheet 1 (original) against cell A1 in spreadsheet 2 (final), in spreadsheet 3. Spreadsheet 3 shows a "-" if the cell they wrote was OK, and shows the new, correct value if they made an error, as follows:
=IF('Final'!A5='Original'!A5, "-",'Final'!A5)
The problem is, this only works if I take the original spreadsheets, which contain formulae in each cell, and paste them into a new spreadsheet using paste values.
Is it possible for the IF function to search the formula in a cell, and not the value?
I have code that "mostly" works great. I'm copying columns from one sheet to another sheet, but in different columns. I have unique code for each copy/paste. The source is mostly raw data. However, there are a few columns that are formula-based, and I'm having a problem. I've attached my code, and it works, but it takes 20 minutes to complete.
I'm very new to macros, but I think(?) I know enough that a data source with 2,000 rows and 30 columns shouldn't take 20 minutes to complete the macro calculations. All of the columns (copy and paste) in the code are pure data. The only exception is the column labeled "AI". Is there are shortcut, other than creating new columns in the source data sheet and pasting these results as values? I put the specific pieces of code that I'm referencing in bold.
I have a requirements to capture the data and calculate the qty based on the defined work week. How to to this in vba macro or a formulas. I'll giving a sample data as reference. In my sample data i have already the formulas but i wanted to automate the work week calculation specially if the number of days in a month has been change.
Btw, the values of every column is came from other worksheet. i copy paste this data as my sample.
For ex : Today is July and it has 31 days, the 31 days will be distributed to the defined workweek and calculate the contents of the corresponding column.
here is the distribution of columns per week as reference.
(31 days) wk1 - Day1 to Day8 (8 colums) wk2 - Day9 to Day16 (8 colums) wk3 - Day17 to Day24 (8) wk4 - Day25 to day 31 (7) columns
(30 days) wk1 - Day1 to Day8 (8 colums) wk2 - Day9 to Day16 (8 colums) wk3 - Day17 to Day23 (7) wk4 - Day23 to day 30 (7) columns
I'm opening a non-excel file in excel and it formats it into a series of rows/columns. The problem I have is that it doesn't consolidate all related data in the same row, for each new column of data it breaks into a separate row. I'd like to easily roll it all up into a single row.
I've attached a sample file to illustrate. The "Original" sheet is what I'm starting with and the "Final" sheet is what I hope to condense the data into.
I am wanting to manage my inventory and need to convert spread sheets with tables specifying style, size color to lists with skus specifying style, size color. please see attached spread sheet with one sheet with table and second sheet with list.
My boss sends me emails with people's information, which I then reformat to put in invoices in word, save as PDF, record in excel, and send out with an email from Outlook. I would love to find an easier way. I'm wondering about putting the info in an excel table so that I can then use a mail merge to create an invoice with the address--unless it turns out to be more work than to format each one (I'm newish to Excel).
The info I get is listed in the following way:
First Name: Mia Last Name: Kant Email Address: mika@gmail.com Country: Afghanistan
[code]....
I need to use some but not all of the info, but if I could get it to format into a table, I assume I could then pick and choose.
I am trying to convert the data I have to matrix format. I have attached a sample of the data in the file I have attached. The data is in the worksheet "Original" and the matrix format that I am trying to convert is in the "Transformed" worksheet. Can someone please help me with the VBA code for going from "Original" to "Transformed"?
I have a spreadsheet of measurements which i need to convert. In short, all of the numbers must be multiplied by 2.54. I can follow how to do 1 number at a time, but the sheer number of calculations makes this highly inefficient. I think my problem lies in settng up the formula properly. I am working with an iBookG4 with an excel program 11.2.3.
I have a portfolio of 23 funds and I need to calculate IRR for each one every month.
The problem is that I have dispersed information for each fund.
For instance in sheet CashCallsBD I have a table with the funds cash calls. This table has a first column with the Fund Name then other column with the cash call date, another column with the cash call value. This table may have other columns. One fund may have several cash calls.
In sheet DividendosBD there is a table with some columns that refer to cash distribution movements. The main columns relevant for this case are Fund Name, Date and finally Cash Distribution (signed in red in the attached document)
And then my problem: In order to calculate IRR for each fund I need to create a table (for each fund) that merges and sorts the information of CashCallsBD, Dividendos BD and present market value for each fund and then apply the XIRR function. For each fund this sorted table would have at least 2 columns (date and values).
I set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.
All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.
From a table like the one here, how can I generate a list (without spaces) of all the names only? Perhaps filtering by "Mr","Miss","Dr" or something? The result I'm after would be a column on a new sheet that ran:
Mr James Mrs Milly Dr McAllister Miss Aujard Mr Barker Mrs Stanley
Our system can create an SLK file showing customer open orders. It is ALWAYS in the exact same format. I created a macro to open this file, manipulate the data (convert text to numbers, convert text date to actual date, and a few other things) and then make a pivot table.
When I try to run it it gets hung up on the pivot table and the debug highlights the BOLD below:
I wanted to see if it is possible to copy a table from one sheet to another sheet, but only the values of the cells, without the formulas in it. As there has to be a different calculation in the new table, when the calculations are done, the numbers go all crazy in the Row that has a formula in it.
This is the code I have got so far: Sub Copy_fromSheetinMA() Dim CellValue As Range Sheet2.Range("Table1").Copy Destination:=Sheet1.Range("Table2") For Each CellValue In Range("D2:CW50") CellValue.Value = (CellValue.Value) * (135) Next CellValue End Sub
in the attached WB you'll find a Source Table that should be duplicated and in the same time sorted as per the Points. The task should be accomplished with NO VBA, NO Helper Columns and NO Manually Copy+Sort.
Column "H" formula was no problem but I'm stuck with the "G" formulas as you can see in cells G5 and G7. I found it difficult to handle two different names with the same points. What would be the correct formula for column "G" ?
I have a adjusting table which auto sorts based on the latest weeks results.
If one of the results is an N/A (There is no data for the site that week, the table will auto sort this value to the top. How do i get it to move any errors to the button of the table?
I've attached a sample worksheet : Table example.xlsm
I am working on a macro to insert a row below if a cell with a validation list has an item from the list selected. The new row needs to maintain the same format and formulas as the original row. To explain this better I have attached a very generic spreadsheet Called Custom Order. In this example cell A3 has a validation list. If the user selects one of the items in the list... I need the macro to insert a row below and maintain the format and formulas found in row A3.
Also this is posted on another forum. http://www.excelforum.com/showthread...=1#post1893257.........
