I was wondering if it is possible to write a formula so that the below table can be read based on the input (in this case start month and cut-off month) and return the value from the table. I have also attached the excel with the data and some examples.
Start Month >>>>
AprMaiJunJulAugSepOktNovDezJanFebMrz
"Cut-off Month
[Code]....
I have a situation where I have to curve fit data, this can lead to different formulas being used with varying constants.
Is it possible to pickup a TEXT based formula and related constants from other cells, and then place this into another cell as a functioning formula. For Example
Cell A1 contains the formula as a text string whether it be y=a+bx+cx^2, or y=a+b/x, etc
Cells A2:A6 contain the individual constants, a, b, c, etc
I would then want the VBA to read the text based formula and put it into an output cell as a functioning excel formulae.g
In cell B10: =a+b*A10+c*A10^2
I understand picking the constants up and putting the formula should not be too much of an issue, however trying to insert the variable form of the curve fit is the part that I am struggling with, and am unsure if possible.
how to inverse a 3x4 matrix in excel.
I can easly inverse NxN Matrix ( 3x3 ,4x4,2x2)
The matrix I am trying to inverse looks like this
1 1 1 1
3,5 2,5 1,8 2
17,1 11,1 5,1 6
I have a data chart on 200 people with overlapping membership in 20+ groups, represeted as binary (1=member, 0=notmember), for example: ...
View 9 Replies View RelatedUsing VBA, I wish to work out the inverse matrix of a large matrix (100*100), but keep getting the # Num! Error. I am using the minverse function. I have defined variable as "variant", does this give me the same possiblities in terms of number size as the variable "Double"?
I'm having trouble with a formula. Column A is a list of names. Row 1 is a list classes. What I need to be able to do is select a class from Row 1 in drop-down list in C14 and have it return the list of names in list starting at C17 based on the 'X'. In this example for SCIENCE it should return Tim Mark Jen. The actual file has 50+ names and 50+ classes.
Sheet1
ABCDEFGH1 MATHSCIENCEHISTORYLANGUAGEPEARTMUSIC2
JoeX XX X 3TimXX X X4MarkXX X 5SarahX X X6BrianX XX 7JenXX X 8
KathyX X X9 10 11 12 13 Class 14 SCIENCE 15 16
Required 17 Tim 18 Mark 19 Jen 20 21
A typical Design Matrix is shown in the attached Workbook. There are two domains of Merged Cells that make up the Headings of the Matrix; FRs (Functional Requirements) and DPs (Design Parameters). Given a Hierarchical List of FRs specified by the User, the User would like Excel to bulild the Matrix Hierarchy of FRs automatically (going down the Worksheet). The DP Hierarchy is the same hierarchy, except transposed and reflected across the Worksheet. The attached Workbook has up to seven (7) levels, but the ability to go create up to 10 levels is desired.
View 8 Replies View RelatedIm currently working to speed up my monthly reporting process. Each month I pull data from the ledger by account & office. Below is an example of what the format of the ledger looks like.
My next step is to report the amounts by account number for each office. At the moment I do this manually, which takes a very long time. My goal is to be able to paste the ledger data on a worksheet and have my report populate automatically.
In regards to the account numbers from the ledger, we summarize the accounts using the first 3 digits of the account. For example, account number "1113454" would be considered a "111" account. Account number 3335454 would be considered a "333" account.
************************************************************************>Microsoft Excel - 3Q Global Variance Analysis.xls___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB16C16D16E16F16B22E22B23E23B24E24B25E25B26E26=
ABCDEF1Ledger2******3*Office*1Office*2Office*3Office*4Office*54*SEP-06SEP-06SEP-06SEP-06SEP-065Account*Number*****6111234127.00766.004,446.007,865.005,509.00711135433,000.0034,566.0099,433.00100.00471,612.76811167645,677.009,898.002,212.0034.00877.00911190932,445.00466.0043,435.00343.005,665.00101118881,876.00433.006,566.00900.0020,398.4411113454121.0032.00900.0075,329.3222.001211345517,678.005,443.00434.00233.0064,556.00133331115,658.00875.00222.0032.00654.001433350090,932.007,634.00122,246.002,134.0055.0015333009123.00543.00990.009,005.00888.0016Sum227,637.0060,656.00280,884.0095,975.32570,237.2017******18My*Report*19******20111*Balance*113*Balance*21OfficeBalance*OfficeBalance*22Office*1113,125.00*Office*117,799.00*23Office*246,129.00*Office*25,475.00*24Office*3156,092.00*Office*31,334.00*25Office*49,242.00*Office*475,562.32*26Office*5504,062.20*Office*564,578.00*27******Ledger*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have been using Excel to record the routine daily issue of items to different groups in a matrix layout, I use a different workbook for each month with worksheets for each group. The matrix takes the form of the item issued being the left hand column and the date issued the top row of the matrix, the quantity issued is recorded at the intersection. Each item can have a different quantity issued on different days. I'm using Excel 2011 for Mac but could use PC Excel 2010. Is there a way to convert the data held in this way to a list? What I'd like to achieve is a list showing the Item, the Quantities Issued and the the Issue dates
View 9 Replies View RelatedI have an excel file which is shared by multiple users. I would like to make one person to have read,write or edit permissions and rest of the users to have only read only permissions.
View 1 Replies View Relatedis it possible in Excel to read value from url every 5 mins.
this html url has a time which is updated every 5 mins, I want excel to read that time from that url every 7 or so mins. so after that I can write another code if time didn't change it will notifity me or do whatever I need to do after that.
I am wondering if there is an easier way to look up values in a matrix. I use the VLOOKUP with the vertical values as the lookup_value, then another lookup for the col_index_number to find the horizontal values. The example in the attached file is just a sample of my spreadsheet. I need to apply this to a roughly 10*10 matrix (which changes every now and then) and apply this matrix to over 1,200 rows. Is there an easy way to returne theses values or is the way I am currently doing it the best option.
View 3 Replies View RelatedI have a excel file that opens and will automatically run a bunch of tasks if a certain user opens it. This user is only used to automatically run this excel file, other users need to open the file occasionally to edit emails address, add clients etc stuff like that, that the automation part of it works off.
Currently I have this user running the file every hour using Win7 Task Scheduler. This is working well, except for the time when another user is editing the file on the hour and of course the Automated user gets the "this file is open blah blah blah, open as read only, cancel etc" popup, this stalls everything, and if I dont notice it, it could sit like this for days.
The file runs in Read Only fine, so... Basically my question is, how to open this file as "Read Only" using Task Scheduler? As if it just ran as Read Only all the time then it wouldnt matter what other users were editing at any given time.
One of my ideas was to have Task Scheduler run a VBS script instead of running the Excel file directly, and having the VBS script simply load the Excel as Read Only.
I am trying to analyze a web page and would like to search through the contents of a web table. I need to extract text error messages which can be located at various unpredictable rows / columns in the web table.
How can I do this?
I currently have a report with so many large array formulas that it is virtually unusable. I want to use the DSUM formula in place of my array formulas but I am vexed in regards to creating a DSUM formula that I can put in the top-left cell of my report and then copy down to the bottom right side of my report. Currently, the array-formulas sum data from a large list when it meets two criteria -- one part of the data-record in the list must match the row label of the current row in the report and another part of the data-record in the list must match the column label in the current column of the report. I have found with the DSUM formula that you can use a "formula" criteria in place of a static criteria...the problem I am having is that the part of the formula that points to the data-set must be relative while the part that points to the formulas criteria needs to be static -- in order to be able to make one DSUM formula and copy it down and over I would need my formula criteria to have the opposite setup (with the data-set part static and the formula criteria as relative).
View 9 Replies View RelatedI have chart like below. In empty cells I want either 1 or 0 (1 if software is installed and 0 if not).
Excel
Outlook
Powerpoint
Word
Computer1
Computer2
Computer3
Computer4
Computer5
Data of computers and their software are like this:
Computer1
Word
Computer1
Excel
Computer1
Powerpoint
Computer1
Outlook
Computer2
Outlook
Computer2
Excel
Computer3
Outlook
Computer4
Outlook
Computer4
Excel
Computer4
Word
Computer5
Outlook
So called Matrix Lookup was very close, but it finds data FROM Matrix (aka that first table). Is it possible at all?
Excel and Windows version:
Excel 2010 SP1
Windows 7
Is there a way of reading .csv files and copying the data from say Sheet1 without actually opening the file itself? I have .csv's that will take some time to open due to size so dont really want to open them, but want to copy the data from all of them within a specific folder.
I am running windows 7 x64 and office 2010 x64.
When an Excel document crashes, I get the option to recover the document the next time I open Excel. However, if it is a read-only file, I don't get this option.
Is there any way around this? I usually work in read-only documents, saving my changes to new documents. If the read-only file I'm working in crashes.
I'm trying to open a file on a network drive...but I'm getting the following error message when it opens: "This file may be read-only, or you may be trying to access a read-only location. Or the server the document is stored on may not be responding." Now, the file itself has no rights restrictions and is not read only. It doesn't appear to be locked.
Now, there are other Excel files in the same directory which I could open fine; however, the Excel documents having the above problem all have a little black icon "appears to be a padlock" (image attached) at the bottom left hand side of the Excel file icon. I tried the following:
- Renaming
- Converting to a different file format (didn't work, it won't let me)
- Opening in notepad...etc doesn't work.
This file is dated back in 2004...do you think it's corrupt? Is there anything i can do to open or recover this?
If I type the name Joe Bloggs How can I make whenever i type that name the computer knows to retreive a number or formula from a cell on another or same sheet.
View 9 Replies View RelatedI am using the following code to read a formula as a string. But sometimes the use r may forget to enter formula in the particular cell. In this case I want to check if the first character of the string is a Equal-to Sign (=). In case it is then macro proceeds further otherwise a message prompts user to enter a formula first. how to check if the first character of the string is a '=' sign?
View 5 Replies View RelatedIn cell AD17, I have a formula that returns the number of days or draws(Skips), since the number in cell AD2 has been drawn. The following cells AD18.......... returns the next skip and continues untill all the hits( AD3), are accounted for. My question, Can the formulas be altered to read a "
View 9 Replies View RelatedI have got several columns of data with subtotals, months, total unit per month, total sales per month, Example
jan feb
total unit total value total unit total value
60 £600 35 £180
SOLUTION
dateunitsale01-Jan6060001-Feb3518001-Mar335801-Apr911220
I am having to use = sign (=A10 --) transfer the information to solution, is there a formula that I use instead. I have try $A10,A$10 to ancher the column?. ideal I need formula that read alternative columns for date, unit and sales- that I can copy down?
Is there anyway to read a specific cell from many excel files automatically and add them to a new workbook?
I tried to link that specific cell to a new workbook manually but now I have a problem: If I change the name of any workbook that is linked to the new workbook, Excel can't update the new file name
I need help for reading data from text files and saving it under different columns in the excel file using vba macro.
For example: I have many text files in the following format ....
I've got a spreadsheet that has password protection before you can "modify" it. All of a sudden today, when I open the spreadsheet and enter the correct password, it still opens but only as Read Only.
View 3 Replies View Relatedreading a zero number in a cell
here an example :
on Cell A1 = 01
on Cell B2 = 10
so I write at C1 the formula is =left(A1,1) but how come the number that comes out is 1 not 0 (zero) ?
but if I write at D1 the formula is = right(A1,1) the number comes out is 0 (zero)
how to make the formula that can read the zero in Cell C1?
I would like to know if there is any formula that can read this:
Measure1 Measure2 Measure3 Measure4
Facility1 10 15 11 18
Facility2 20 16 32 21
Facility3 9 18 17 13
All this is in one big table and I have around hundred columns. I wish to create one lookup table that will pull values out of this, on a separate worksheet. This is how my lookup table looks like:
Measure3 Measure8
Facility1
Facility2
Facility3
My raw labels will be complete and they should match with lookup table labels. I need to extract Measure3 and Measure8 for different facilities, depending if I have any data in there. The search criteria for column labels should be dynamic and if I change label for let's say Measure1 it would update for all facilities. What formula can read both raw labels and column labels and return value at the interesection of both these criteria.
For a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.
ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe
I've recently created a macro which takes a .csv file and scrubs the data and dresses it up for a report. I've finally got it running without any problems and have added it individually to 5 different user accounts on my network as an add-in, but need a way to share it across a computer network without allowing others to edit the file.
The report is generated multiple times a day by the different users, none of whom are very computer literate (not that I am much better, myself), and the macro over-writes the old file each time. I've read that it's possible to simply create a shortcut to the file that automatically opens the spreadsheet in read-only format, which would be perfect for what I'm trying to do, but I haven't been able to get it to work so far.
My current shortcut pathway is similar to this:
"C:Program FilesMicrosoft OfficeOffice15EXCEL.EXE" /R "C:UsersBenDocumentsOT Master.xlsx"
On my computer at work (Windows 7) the shortcut simply opens the spreadsheet so that anyone can edit it, instead of as a read-only file. On my computer at home (I'm trying to experiment) (windows 8.1) it won't even let me save the shortcut, saying I need to provide administrator permission & then cutting to a window that says "access denied".
I won't have the pathway for the actual file I'm trying to create a shortcut for until tomorrow when I get back to work, but I've formatted it essentially the same, directing to the excel.exe file, running the switch "/R", and then directing it to the actual file location.
The ultimate goal is to have multiple people be able to view the file without locking out those who need to update it.
