I have a sheet connected to external SQLSERVER data.
One of the columns includes a formula, but the formula is not evaluated but shown as text.
My challenge is making it evaluate (without using excel tricks like replace equal sign with equal sign or clicking each cell and approve it)
I tried going down the road of concatenating char(10) or char(13) to the formula string but no success.
Say I have Source Workbook X 1, and Destination Workbooks X 4. The Source workbook contains a number of lookup values. I want the lookup values to be maintained in the single Source workbook, and I want this workbook to remain closed when the end users are using their Destination workbooks. It is critical that certain cells in the Destination workbooks have validated data in order for formulas and summarizations to be correct.
I'm happy if the Destination workbooks contain a lookup worksheet (perhaps hidden). In short, I'd like the contents from the Source workbook, Lookups worksheet, to just be replicated "as is" into the Destination workbooks, Lookups worksheet. I then want to do my data validation from that worksheet, which of course is open (data validation appears to only work with open workbooks).
I've investigated the techniques in this link: [URL]
Questions:
1) Are the techniques in that link about the best approach? i.e. an external link plus array formulas?
2) A blank cell in the source workbook (text column but formatted as General) is resulting in a zero in the external link. How can I make the external link exactly match the text as entered in the source workbook?
I have a cells that are formatted for accounting with a formula in them.Whether the formula is evaluated or not, the £ sign still shows. Is there a way of only getting the £ sign to show only if the formula IS evaluated (i.e leaving a blank cell if the formula IS NOT evaluated)....
View 6 Replies View RelatedI have a nested if statement that doesn't give a blank cell when it is evaluated. The cell is general formatted but results in a '0' in the cell instead of a completely blank cell.
If statement is:
=IF('Sales info'!B16="",'Sales info'!B6,IF('Sales info'!B6="","",'Sales info'!B16))
Why is this giving me a 0?
I have some sumifs() functions in cells in a workbook that contain external links in them:
Code:
=SUMIFS('C: empForAuditor[Budget Employees 2013 (0055521).xlsm]Labor Summary'!$E$54:$E$88,'C: empForAuditor[Budget Employees 2013 (0055521).xlsm]Labor Summary'!$C$54:$C$88,$G50,'C: empForAuditor[Budget Employees 2013 (0055521).xlsm]Labor Summary'!$A$54:$A$88,H$49)
The formulas work fine if the external file is open. However if the linked file is not loaded and I load the file with the external links, as soon as the file with links is calculated, some of externally linked cells turn to "#VALUE" errors. I have a need to have only certain people see the employee file that is referenced above but the others need to update the file with the external links and not have errors in cells.
The really weird thing is that some cells yield the #VALUE error and some don't, even when both have SUMIFS() functions in them. Even cells with the Exact same formula in them will sometimes yield #VALUE and other times yield a real value.
When I open my Excel workbook, I get the following error:
Removed Feature: External formula reference from /xl/externalLinks/externalLink1.xml part (Cached values from external formula reference)
I am asked if I want to recover the contents of the file.
(1) I have conditional formatting working to format cells that contain a formula.
(2) I need to also separately conditional format cells which link to a formula in an external worksheet.
I don't wish to use VBA and would like to use defined name range method outlined below.
To do (1) above:
Under define name range, created "cellhasformula", the formula used in the field refers to was: =get.cell(48,indirect("rc",false))
In conditional formatting using: format values where this formula is true. =cellhasformula (works great).
These formulas in their current form, do not work in defining name range:
=InStr(1, c.Formula, ".xls") > 0
or eg =InStr(1,rCell.Formula,"[")>0
exact formula to use in define name range to return a true value if a formula is linked to an external worksheet?
We're using SUMIF and it won't work unless the external excel file is open.
This is the formula we're using:
when i use a simple sum it only returns 0 but if I use the same cells and manually add them ie A1+A2+A3 and so on it give me the answer ? As there are over 500 cells it would be a pain to type them.
View 9 Replies View RelatedI have a workbook that is taking the avarage percentage of efficency over a possible of 7 days. If there were only 5 days of production it would average only 5 days. It pulls info from a tab that is call "Datalink" which is just that. As we have started our new fiscal year the person who uses this came to me with this problem
1) GrpIV on C15 is returning # DIV/0! and we need it to return 0. See the attachment.
I have always used unique ways to randomly make selections in my life, and was thrilled to learn that Excel could pick a name(not just a number) from a list. I've used the =INDEX($A:$A,RANDBETWEEN(1, COUNTA($A:$A)),1) function and entered items into Column A (or another if needed, and updated by trial and error accordingly). It worked very well for me for a long time and then stopped working. In multiple documents, with multiple lists (not connecting to each other), all of a sudden every random item started giving the #NAME error.
View 5 Replies View RelatedAn external data web query points to a web site that offers a foreign exchange rate calculator. In my browser, I selected the specific currency pair I need and used the resulting url in the web query. The "New Web Query" pane resolves the url correctly and allows me to select the table data I want (the little yellow arrow turns to a green check mark). However, when I hit "Import" and select the target cell, the only data returned is the parameters from the url and an error msg (This web query returned no data...). How do I get the query to pass the parameters to the website correctly?
View 3 Replies View RelatedI want to be able to get a value from a specific cell in another workbook, the problem i have is that i want to be able to select what file the active workbook looks for from the start page of my current workbook. For example when i load the workbook i get my startup page ( sheet 1) where i will be able to select what external workbook the active workbook will be linking with, i will then link the active workbook to the value in a specific cell from the selected external workbook.
I know to link to another workbook i would use ='[filename]sheet!cell
However i want to be able pull up a 'open file dialog box' which i select a file name which will be used as 'filename' in the formula above.
I have a sheet called "Sheet1" in workbook "File1". In this sheet, I have a lot of formulas and some of them involves linking to external files located in E: Finance folder. Is there a way to automatically highlight those cells containing a formula that references to external files?
View 3 Replies View RelatedI need a formula that can return the value from a cell in the same row it searches. The row is unsorted and has blank cells. Seacchs row A12:X12 returns the text in C4 (data validation List) in A19 on a different sheet.
View 6 Replies View RelatedThe results of the formula in cell K36 in the attached spreadsheet returns a value of null. It should be $1,200. Am I blind or have I done something wrong.
View 7 Replies View RelatedHow to make vlookup work right? I have tried it once and it worked but I can't get it ot work again.
I changed the format to text for the data I using and the data I looking in to see if that would fix the ref# or N/a error.
Is the formatting one possible issue?
I am slow to vlookup but I used the formula as
1. the lookup value is the serial #
2. the table_array is the entire worksheet that I am searching in - or should I just use the column that hold the values I am looking for?
3. the column_index_num is ??
4. [range_lookup] is FALSE.
I have a formula that looks like this:
=IF(ISERROR((C101/G119)*G104)," ",((C101/G119)*G104))
There are about 15 cells with formulas similar to this. They are calculating a value for a row and at the end all of them should add up should equal C101.
The problem is the value that formula returns has to be two digits and I know it almost never is. But it ALWAYS equils C101. However because it only shows two digits somtimes if you manulally add up the value that you see it does not its off by +/-.01.
I need to fix it so that it does equal C101 AND if you were to add up the digits (with 2 decimal places) it will also equil C101.
I placed this formula in cell A1 in workbook number 2which is suppose to grab what ever name is in cell A4 of workbook 1 However if the cell is blank it is returning a "0"I want it to be blank if there is no name in the cell?
View 4 Replies View Related=IF(O23="","",IF(SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23),'Container Price List'!$F$2:$F$19959),SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23),'Container Price List'!$F$2:$F$19959)*R23,SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23)*('Container Price List'!$L$2:$L$19959))/SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23)*('Container Price List'!$J$2:$J$19959*4.33))))
For some reason it is returning #VALUE! in some cells. I think its something to do with the last part of the formula where it is bold.
The formula as you can see looks at data and matches relevant data to return specific depending on what has been matched. Now at the end it is matching the correct data and return that value however i want to divide the value by another (another value*4.33).
I need a dynamic formula in A1 that returns min value in column B in a range of the last input and 15 cells back.
Then I need another dynamic formula in B1 that returns max value in column B in the same range.
Can an if statement return a formula (e.g. =IF(A1=1,C1+D1,0))
View 9 Replies View RelatedThe formula below was written a couple of years ago and I just discovered an error. It returns an "S" for the value 0789 when it should be a "C", (Consecutive).
IF(OR(AND(ISNUMBER(MATCH({0,1,2,9},$O215:$R215,0))),AND(ISNUMBER(MATCH(MIN($O215:$R215)+{0,1,2,3},$O215:$R215,0)))),"C",IF(SUM(IF(FREQUENCY($O215:$R215,$O215:$R215)=3,2,1))=2,"DD",INDEX({"S","D","T","Q"},MAX(FREQUENCY($O215
******** ******************** ************************************************************************>Microsoft Excel - FL MID PLAY 4 CONSECUTIVES.xlsm___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutT215=OPQRST215078907893SSheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Some reason my sumproduct formula returns #Value!
=SUMPRODUCT((Data_2005!$A$2:$A$170=A$2)*(Data_2005!$F$2:$F$170=$A5)*(Data_2005!$S$2:$S$170))
wb is attached (stripped down for upload)
I have a formula in a cell which is refered to other cell within the same sheet and also to other sheet from other file. say
C1 = A1+B1+data file c1 Data file is another file in other location.
I was trying to break a external reference link (from Data File) from Data _ Edit Link, but it is converting all the formula to value. How is it possible to break only the external link without damaging the formula within the sheet say c1 = A1+B1+26 (value from data file c1)
how to change the formula below which is referencing an internal worksheet, to a forumula that will reference an external worksheet that is located in an excel document on the G: Drive.
=SUMPRODUCT(--(1210laradj_v_2009!F2:F65536="HMO-ALT SELECT"),--(ISNUMBER(MATCH(1210laradj_v_2009!AO2:AO65536,{3,5,9,12,37,39,46,47,54,73,74,75,76,77,78},0))))
So what it's doing is totaling the counts in the 1210laradj worksheet that match columns HMO-ALT SELECT as well as the numbers 3,5,9 etc.
Well now these worksheets will be on a seperate excel spreadsheet located on a network drive, so I am trying to figure out how I can fix this.
I tried adding it in like so, but it fails.
=SUMPRODUCT(--('G:ROC-CLAIMSAudit ResultsProvider infomonthly statsReproc'd claims2011ProductionCounts[0111rpt2235_laradj_v.xls]0111laradj_v_2009'!F2:F65536="HMO-ALT SELECT"),--(ISNUMBER(MATCH(1210laradj_v_2009!AO2:AO65536,{3,5,9,12,37,39,46,47,54,73,74,75,76,77,78},0))))
I'm trying to make a spreadsheet that references data tables I have pulled from the internet, with the hopes that these tables will update as I refresh the data connections. I'm in the early stages of creating this spreadsheet right now, but whenever I try to refresh my data connections, it takes a tremendous amount of time. I have about 6 connections with 100 row lines total. Is this typical with data connections to the web? Is there a better way to do this?
View 1 Replies View Relatedi have to take the PGN chess game notation file into excel.it is a text file from notepad. it is like this as shown hereunder. how can i get this into columns movewise. ex.1.d4 Nf6 should come in A1 and 2.c4 d6 should come in A2.
1.d4 Nf6 2.c4 d6 3.Nf3 Nbd7 4.Nc3 e5 5.e4 Be7 6.Be2 O-O 7.O-O
c6 8.Qc2 Re8 9.Rd1 Bf8 10.Rb1 a5 11.d5 Nc5 12.Be3 Qc7 13.h3
Bd7 14.Rbc1 g6 15.Nd2 Rab8 16.Nb3 Nxb3 17.Qxb3 c5 18.Kh2 Kh8
19.Qc2 Ng8 20.Bg4 Nh6 21.Bxd7 Qxd7 22.Qd2 Ng8 23.g4 f5 24.f3
Be7 25.Rg1 Rf8 26.Rcf1 Rf7 27.gxf5 gxf5 28.Rg2 f4 29.Bf2 Rf6
30.Ne2 Qxh3+ 31.Kxh3 Rh6+ 32.Kg4 Nf6+ 33.Kf5 Nd7 34.Rg5 Rf8+
35.Kg4 Nf6+ 36.Kf5 Ng8+ 37.Kg4 Nf6+ 38.Kf5 Nxd5+ 39.Kg4 Nf6+
I am creating a tool that many people will be using. I need a way to do the following from vba:
1. Click on the "From Web" button under Get External Data section in Data and show the web query pop-up
2. Input the url into the field and press enter
At this point, it will be the user's responsibility to log in w/ their username and password. I have tried to record a macro w/ no success. I just need to get the query open for the user without them having to do anything.
I have some data in tabular form on a sheet. I want to represent this data on a different sheet, but have it be dynamically updated. I'm thinking that the smartest way would be to give the original data a named range, then do some sort of external data referencing to the source from Data - Import External Data. But this sounds kind of silly.
How do I do this? And no, I don't want to use pivot tables because I don't have statistics to populate in the cells...I just want to display the data as is.