I want to create Name to include data in A101, C101, E101, G101, I101, K101, M101 and O101. How do I do it? I tried to hold ctrl, then click on each cell above, type name but it did not take.
View 9 Replies
My table is a much larger version of this:
A B
X1283.786
Y1684.379
Z1719.013
X10745.013
Y10860.668
i want excel to work out the total for x y and z. however i have about 100 different things x y and z could be and its a long list so an automatic way would be great.
In worksheet "Details" search down column "A" looking for any text/numbers. If there is something entered in the cell then look at column "M" for the same row and if that cell is empty inset "0%" in the cell.
Example:
Cell "A1" contains 12345. Cell "M1" is blank. The macro would inset 0% in "M1".
Cell "A2" contains 54321. Cell "M2" contain 40%. The Macro does not modify that row.
Cell "A3" is blank. The macro does not modify that row.
Cell "A4" contain 65478. Cell "M4" is blank. The macro would inset 0% in "M4".
I have two columns with correlating data. i created a validation drop down list and would like the corresponding number to populate a cell based on the drop down list selection. i.e....
View 2 Replies View RelatedI have a worksheet that I would like where you can just enter the date, then it builds a filename based on that.
Example. Filename I would like to load information from is
"C:EXMay 2009May 21,2009.xls"
The sheet is "Data" and the cell/row is A1
The Filename could be entered simply as
'C:ExMay 2009[May21,2009.xls]Data'!$A$1
But what I would like to do, is be able to generate a weeks worth of filenames from the date I entered.
I have one cell with the root directory (lets say A1)
A1 = C:EX
A2 = May
A3 = 21
A4 = 2009
So in cell A5 I would like to combine them all to load the information from that filename.
I tried to do something along the lines of
A5 = A1 + A2 + " " &A4 (etc etc), but that wasn't working at all.
The main reason for breaking them up is so that I could just increment the date to get a weeks worth of files.
I am trying to use Excel to document fiber optic cables that run between different locations. Each cable has 2 ends, but whatever service is connected to one end, will also be a the other. I would like to use an Excel worksheet to represent a termination point (a "light shelf" comprised of several fiber optic cables each made up of several strands of fiber (kind of a table with the 1st 4 columns of 6 rows being cable A, and the next 8 cable B)). I would like to be able to type in the value for cable A Strand 1 and have it linked to another worksheet representing a different location (the other end) and vice versa. I have tried to paste a link, but that seems to get broken. Is there a better way to make permanent links (Excel is bawking about a circular reference, which it is as each end needs to be linked back to the other.
View 9 Replies View RelatedI am trying to find a way to create new sheets in a workbook that when created will reference a new row on a data sheet in that workbook. Each workbook could end up containing varying numbers of sheets (anywhere from 2 to 150+) based on size limitations. There are some lookups and referenced data on each sheet that would be copied as is and not reference the data sheet. I have not included these in my example.
I'm trying to avoid having to change each cell's reference manually for all of these sheets (which is one of the solutions I've come up with so far). My current method of creating these workbooks does not involve the data sheet and each workbook starts off with data only on sheet1. I select and copy that sheet into sheet2 and every subsequent sheet. I want to incorporate the data sheet so it can be filled out by someone else and copied into that tab to populate all of the data sheets.
first post, i'll try to make this short and to the point. i have a file that has single invoice numbers, invoice dates and invoice totals. the line does not have a vendor name. the vendor name is above the invoice lines. (see spreadsheet).
Example:
D2 = Vendor
Lines 3-10 are individual invoices
Basically, I need to setup a formula that says if a cell in Column D is blank, then select the closest cell above the blank cell. I have 65000 lines with thousands of vendors. In the end, each invoice line will now have the vendor name that it is associated with.
I am trying to do is, to sum up the values in an array, given that the cell value is not an error.
If the cells were in order, the following array formula would solve it easily:
{SUM(IF(ISERROR(A1:A3),0,A1:A3))}
But my problem is that, my cells are not in order. To be more specific, I want to look at A1, B12 and C13, and sum them up with an array formula given the condition that cell value is not an error. Of course, in my case, I have too many cells.
it possible to create a pdf files using a macro?
I would like to take cells b5: j40 from sheets , W 1, W 2, W 3, W 4, & W 5 and place it on a pdf.
Is this doable?
I am trying to create a range with visible cells after filtering based on a criteria. But, facing some wierd problem with setting correct range with visible cells. Here is the code.
Code:
xlsDynRange = xlsWorksheet.Range("A1:G500")
xlsDynRange.AutoFilter(Field:=1, Criteria1:=intEmpid)
xlsFilteredRange = xlsDynRange.Range("A1:G500").SpecialCells(Excel.XLCellType.xlCellTypeVisible).Cells
Here, xlsFilteredRange.Cells.Count returns correct values. For example, for a particular filtered criteria, if there are 6 rows, it returns 42 (6 rows * 7 columns). However, xlsFilteredRange.Rows.Count always returns 1 (header row), though there are 6 rows. How can this be fixed?
I am creating one big workbook with several different worksheets. I need to make the first two columns of every worksheet identical.
I would like to make it so that when an item is changed in any cell in any column on any worksheet, the other worksheets automatically change to become identical.
Semi-Related topic: http://www.mrexcel.com/forum/showthread.php?t=434301
what i was wondering is if there was a way to take a list of data (only look at the letters before the "-") and make a list of it..than i column "T" use a countif formula (i can do this part just forgot to include it)
so if "MCS-69257" was added to the list in cell "C8" than in "S6" it would say "MCS" and "T6" would say "1"
Sheet10
C2ABC-259153CXS-280374XCG-265065TAS-199816ABC-114197CXS-21045
Excel tables to the web >> Excel Jeanie HTML 4
Sheet10
ST2ABC23CXS14XCG15TAS1
Excel tables to the web >> Excel Jeanie HTML 4
I have a formula which looks in a folder for a file and returns the cell I want from that folder and work book
='H:NCHOCall CentreCall Centre ResourcingStats[NHC_Telephony_Daily_2012-11-27.xls]NHC Daily'!$E$55
as you can see I have a date in the middle, I want it to reference to a cell with a date in and return the information from that document with the change in date. So all I have to do is drag the formula down and it will return the info i want out of each report.I think it might be an add on that I need to download?
Based on a Beginning Date Value and an Ending Date Value - I would like to color a range of Cells - the month (dates) are in the header row.
For example: Beginning Date = 01/01/09
Ending Date = 06/01/09
I want the range of cells in the same row colored orange under Jan '09 to June '09.
here is the snippet of code I'm using
View 11 Replies View RelatedI have a sheet that has 5 columns as shown below, xxx represents the values within the cell that I need in each sql file.
A |B | C| D| E
test1|xxx|xxx|xxx|xxx|
test2|xxx|xxx|xxx|xxx|
test3|xxx|xxx|xxx|xxx|
test4|xxx|xxx|xxx|xxx|
test5|xxx|xxx|xxx|xxx|
What I am trying to do is take the context of each cell starting with B2, and place it in an Individual sql file (or text file with .sql extension) and name it A2 (so test1 in this case).
I tried creating a double for loop, one for last column, one for last row, taking each content and placing in file but I am not sure how to take the column A as the name of the file and to have 1 file per cell.
Sheet11
EFGHIJK8480NVF-25747NVF25747
8581ATT-67546ATT67546
8682RTC-35615RTC35615RealRTC-35615
8783CSX-45715CSX45715RealCSX-45715
8884CSX-24915CSX24915RealCSX-24915
8985HTC-56947HTC56947
9086MCAS-85415MCAS85415RealMCAS-85415
9187BNSF-77716BNSF77716
9288MCAS-97316MCAS97316
9389CLRV-24760CLRV24760
9490CSX-24101CSX24101
I have a thing about named ranges and cells, but was wondering if there is a way to create names for multiple cells using set criteria. I think the best was to explain this is with an example that I have attached. Ideally I would like to name the individual cells in the range C3:d4 by concatenating the right column and bottom row to give one unique name. However this doesn't combine them. I have listed the names that they should be and corresponding cell in C8:D11
View 2 Replies View RelatedI am trying to create a function that will evaluate a cell, lets call it B10, and depending on this numeric entry input data in cell ranges B14,B22.
What I am trying to do is create a list of locations for a packing slip that when I enter the corresponding location number (i.e. 200) that it will populate the shipping address in B14,B22.
Is this possible?
creating a formula to rank certain cells in reverse order.
Example
Cell Total Rank
B1 = 2456 3
B15 = 2369 2
B30 = 2547 5
B45 = 2141 1
B60 = 2471 4
Rank every 15 cells basically in reverse order.
I had posted earlier to see if there was a way to protect certain cells from being updated when using a linked excel spreadsheet to access. Since there have been no replies I'm assuming not.
Different approach, can I create a Macro that would run after the update that would insert my required text into each cell from A2 through to R2.
The text would always be the same and always in exactly the same cells. This would need to be an insert into and not a replacement of the data already in those cells.
A1 B1 C1 D1
State
BUName
InsuranceCompanyName
Claim
ROnumber
Renter Last Name
[Code] .......
A2 B2 C2 D2
CategoryFilter CategoryFilter CategoryFilter NoFilter
The text under each of the A2, B2, C2 etc is what I would want the macro to do for me, essentially putting back what the update from the linked source removed.
I am trying to create a macro that will create a number of check boxes, which are linked different cells. I have had some success in creating multiple check boxes and having them at the destination I want the problem is that instead of linking to different cells they are all linked to the same cell. I have attached a sample workbook SAMPLE.xlsx
View 1 Replies View RelatedCell F9 looks at D9. If there is no value, I would like it to return "", if there is a value, I'd like it to finish the formula.
It figures the formula correctly, providing there is a value in D9.
However, the entire D column is referencing cells from another workbook.
So while no value shows up, if you click on an empty cell in D9 it says:
='C:Workbooks[EnterMenu.xls]Enter Menu Items Here'!D5
How can I get Excel to see 'visually' empty cells in the D column as empty when it seems Excel is reading the cell value as a reference path.
However, that diagnoses might be wrong, since if there is a value there, it figures the formula using the value and pays no attention to the reference path.
This is the formula I'm using currently:
=IF(D9=0,"", IF(N(H9),H9/D9,G9/D9))
Im trying to create shapes Rectangles and Squares with different colours within an excel sheet, where the length and height of the shapes is generated by input values.
Also is it possible to create 3D shapes, again where the size of the shap is generated by input cell values.
I am trying to build a selection for a procedure to use.
View 2 Replies View RelatedI need some help about creating add-ins to distribute my macro.
I have written a macro and have saved it as an .xla file.
What i want is that when this add-in is installed an extra button appear on the menu bar and when users click the button the macro should run.
I tried to install the add-ins but am having some trouble in creating a button automatically when add-in is installed and assigning a macro to it?
I tried to look through some books and on some internet websites but really cant figure out.
I have an issue creating a PDF of more than one sheet. Only top left corner of sheets are shown in PDF. Creating a PDF of a single sheet works perfectly. I have attached the result (2 pdf-files) and the original Excelfile.
Code for one sheets (Result OK):
[Code] ......
Code for multi sheets (Result not OK):
[Code] .........
Attached Files
CreatePDF.xlsm
MyPDF_OneSheet.pdf
MyPDF_3Sheets.pdf
I am unsure if this is supposed to be in the PivotTable or VB section, but given it is VB code I expect here is better.
[Code] ......
I can confirm the SourceData exists, and can be used to create a pivot table without error. Only when I try to RUN this code does it kick out saying, "Invalid procedure call or argument".
I am tying to build a macro that contains a function to open a closed workbook, create a new tab, select the tab and then paste data into it before closing the workbook.
In a separate macro I will then want to open the workbook again and compare the last two tabs worth of data, to highlight where old data has been removed.
