We help IT Professionals succeed at work.

Spreadsheets

A spreadsheet is an interactive computer application program for organization, analysis and storage of data in tabular form. Spreadsheets developed as computerized simulations of paper accounting worksheets. The program operates on data represented as cells of an array, organized in rows and columns. Each cell of the array may contain either numeric or text data, or the results of formulas that automatically calculate and display a value based on the contents of other cells.

I have a SSRS report and the cells in one column (MarketPrice) can be a number or text "NA". Is there a way in SSRS to have numbers in raw excel show as numbers even though another cell value in that column is text (NA)? Right now the raw excel dump shows the "Number Stored as Text" green triangle for numbers.Column with Number and Text values.
Thanks in advance.

Rob M.
0
I am trying to compare two separate sheets in Excel.  The problem is, each row does not match to the adjacent row.  I want to select a row and compare it to another row displaying what is missing in in either sheet.
0
I am trying to import the table data shown here into this Google Sheet complete with pictures.

https://www.vercalendario.info/en/what/utc-offset-by-country.html

The fields needed are
UTC
Country Flag
Country
City
Date and Time Link

I have started a sheet here complete with the field names, and a link to the table data in a separate tab.
https://docs.google.com/spreadsheets/d/1U3kMIOyXCIbkbjE70OMQBtJCJv_vCkCQZtW8F35_7C0/edit#gid=0

Assistance is greatly appreciated.
0
I need some assistance with an (If and) formula. At least I think it would be an (If and). I am trying to set up a formula to do the following:

1) You manually choose if an employee will require a medical (based on the position)
2) If they do require a medical, then I have a formula that is working that calculates the frequency of the medical (for example: if they are 50 or over then they need a medical annually, but if they are younger than 50, they only need a medical every 3 years) This formula works well.
3) I already have the formula to calculate their current age and deduct that from their year of birth (In cell D6)If--and--And-Spreadsheet-Example.xlsx

I think that the formula that I need should be something like this:
1) If they need a medical you answer yes using data validation (This is in Cell B5 on the attached sample workbook)
2) You input their date of birth in (Cell B3 on the attached sample workbook)
3) The frequency of the medical (cell B6 on the attached sample workbook) needs to have formula for the following scenario:
- If you answer Yes in B5, then in cell B6 this is the formula I have =IF(B4>=50,"Annual",IF(B4<50,"3 Yearly"))
- If you answer No in B5, then I want Not Applicable to show in cell B6

I would appreciate any assistance with this formula.

Regards
Dot
0
Hello Experts,

One of our users is unable to make a connection from her locally installed copy of Excel to a remote SQL server.  We have a number of users who do this on a regular basis, and have spreadsheets with stored and configured data connections.  When she attempts to refresh the data through the connections stored in one of these spreadsheets she receives a message that includes "provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.".  I then attempted to make a connection from her system to the SQL server from a blank Excel file, and when it gets to the point where you would typically enter the credentials for the connection, the application hangs with no option to enter the credentials.  In searching on the error message I found a number of articles referencing the need to obtain a certificate from the SQL server and install it on her system.  However, I was able to successfully make the connection through the spreadsheet mentioned above on another system without any need to install a certificate from the remote SQL server.  I have reviewed the settings in the Excel Trust Center on her system and to my knowledge they are configured correctly.  I'm currently in the process of making sure any and all available updates are installed on her system, but so far that has not changed the result.  Any ideas on how to successfully make the data connections from her system?

Thanks in advance,

Russ
0
Ink
Hi:

The above screenshot shows that the Power BI refresh (both manual and scheduled) failed due to invalid credentials.  We have tried changing credentials and other properties but to no effect.

As you can tell, the two connections are represented by shared paths to Excel spreadsheets.  And, these spreadsheets pull data from a SQL view successfully.

This virtual machine from which this screenshot was taken pulls data from the box that has a personal gateway installed.  But, the virtual machine itself does not have a personal gateway installed.

What is the remedy for this?

Thank you!

Software Engineer
0
In the attached file I'd like to keep the cell references the same despite the fact that I have to update the data in cells C2:H2.

To update I click inset a row which means the data moves down. When this happens the cell references increment one every time I add a row. That means instead of remaining "C2:G47" the cell references increment by one as shown in the formula below. The formulas  are in cells Q4:Y11.

=IFERROR(AGGREGATE(15,6,$C$3:$G$48/($C$3:$G$48>P4),1),"")

If it cannot be done with the formulas and data in same sheet.  I will need the code to use in the formula to reference from the new page to the data page.
KeepSameCellReferenceWhenMovingCell.xlsm
0
Hello all,

I am looking for a way add a (dotted line) projected value into a Google Sheet chart. I am plotting two sets of numbers on a graph, but the final averages for January are incomplete, and therefore show a misleading result. The averages in columns B and C are pulled from collected data in columns P ->.

The projected value could be either calculated automatically, and based on the total number of reported values in time elapsed. Meaning that the value will continue to go up. The value equals the number of days past, up to a maximum of 50.

If automating the projection is too complicated, I will also accept inputting manual value. I can work out my own projection analysis later. Right now, I am more interested in knowing how to create a chart like my mockup; see sheet.

In the example: https://docs.google.com/spreadsheets/d/1Q2nN-MVD_iwJi2Se-PFWcNbMGb-EF6gQWQCruhnWW-Q/edit#gid=180731546
See chart experiment tab.
0
excel 2016 compare two worksheets in different workbooks for differences. I have similar data on spreadsheets in 2 different workbooks and would like to compare them for differences.
0
I am having trouble with a formula to return distinct numbers in a range of cells. In this case the range of cells is C2-G46 all of which only contain numbers 1-99.

Using the following array formula:

{=IFERROR(INDEX($C$2:$H$46, MATCH(0, COUNTIF($O$1:O1, $C$2:$G$46), 0)), "")}

returns blank. I have pressed CTRL + SHIFT + ENTER to get the curly brackets required in an array formula but not getting the results I want. Instead a get a blank.

I am interest in a list of unique numbers in sequence beginning in one cell and continuing left to right until all unique numbers are listed. The numbers can be from 1 -99 in case that is needed for a different solution.Unique-DistinctinArrayC2-G46.xlsm
0
I am using the IMPORTRANGE function to pull in three columns from another sheet. The columns are:

Name, Option 1, Option 2

But I need a way to filter out any names that include N/A in both columns. In other words, filter out content when two conditions are met.

I prefer a formula based solution, rather then using Conditional filtering. The solution can use hidden columns if needed. Failing that conditional formatting is acceptable.

Your assistance is appreciated. :)

Here is the working sheet:
https://docs.google.com/spreadsheets/d/130fX-RhlHWxHHQpaKAGzrd7LPVFTM6gEdJqPQ4msTqY/edit#gid=0


Thank you.
0
Hi expert,

Sheets was last year so easy to use, so fast & simple.
But I don't know why formulas aren't working anymore as used to.

I was using the formula "find" in combination with "filter". When I use the formula "FIND", it searches only in the 3th row in its range instead of the whole range.

If I use a combination of Index & Match formulas it works for certain parts but on others not.
I have the formulas that are not working properly shown in red, in sheet "Dashboard".
https://docs.google.com/spreadsheets/d/1waMdthDl_vmRODRqv7O-eD26zBQG57c8McsgjXGYjyo/edit?usp=sharing

Can you help me understand what is happening and how to solve it?
0
I have another Excel puzzle that I am trying to solve.

I am trying to convert a name column into an email list. It sound simple enough, but there is a small twist, that is, some names included preferred first names in brackets, and some names have three names. :P

Note: The names the example spreadsheet are a good representation of the list I will use IRL:
- Only first and last name, if a Western name.
- May include a preferred name in brackets, if the name is non-English - the first name in brackets will be used with the last name.
- Some names have three components.

Examples:
Column A               ->  Desired name conversion

Bobby Robinson            -> bobby.robinson@company.com      - solution found
Lin Hon (Adam) Xiao      -> adam.xiao@company.com               - solution found
Lang Lin Lee  -> langlin.lee@company.com                                  - need a fix.

So the last scenario still needs to be resolved.

The current formula (thanks to Ryan Chong), doesn't take into account a name with three parts.
For example, a name like "Lang Lin Li", converts to lang.lin.li@company.com but should be langlin.li@company.com

I was thinking that I could parse the new solved names column with a =SUBSTITUTE(G9,".","",1), but it would also have to take into account to only remove the first instance of a ".", but only when there is more than 1 "."

Is it possible to add additional complexity to the existing formula?

Current formula:
0
I have another excel puzzle that I am trying to solve.

I am trying to convert a name column into an email list. It sound simple enough, but there is a small twist, that is, some names included preferred first names in brackets. :P
This means that the preferred first names must be used.

Note: The names are strictly using the the examples in the spreadsheet, there are no other variations:
- Only first and last name, if a Western name.
- May include a preferred name in brackets, if the name is non-English.

Examples:
Column A               ->  Desired name conversion

Bobby Robinson            -> bobby.robinson@company.com      
Lin Hon (Adam) Xiao      -> adam.xiao@company.com

In the example spreadsheet (tab = email addresses), I was able to each scenario separately, but not as a single unified formula.
https://docs.google.com/spreadsheets/d/1Q2nN-MVD_iwJi2Se-PFWcNbMGb-EF6gQWQCruhnWW-Q/edit#gid=833560184

Can anyone with better skills than me, help me with a solution?

Thank you
0
Sheet1Sheet2
I have two Google Sheets. Sheet1 is categorizing the "Places" from Sheet2. I want to populate the "Categories" column on Sheet2 with all the headers from Sheet1 whose columns contain the given "Place". For example, the "Categories" cell for "Alabama" should contain "Bands, States" (or whatever concatenation format can be done).
0
Trying to Merge several spreadsheets into a single workbook and get an error. **This used to work running from Windows 7 and now get error running on Windows10, office 365 excel. See attached image and VBA Script below.   Failing at line 11             "Set bookList = Workbooks.Open(everyObj)"

I net get prompted with a Data Link Properties screen and a debug on the line above.

Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
 
'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("h:\Final Reports mdbs etc\AD HOC REQUESTS Clarity_and_Tapestry and Scheduled\Automated Productivity Report\Pends_BEH03_Dropfiles")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
 
'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("A2:CK" & Range("a466862").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate

 
 
'Do not change the following column. It's not the same column as above
Range("a466862").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub

Open in new window

Script-error-Windows10.jpg
0
Hi there. In the attached file, I need to pull back the total amount of each user's donations using an Excel formula.

Any ideas experts?
sum-donations-per-user.xlsm
0
Hi,

I would like the following formula to be modified to return the last value.
The below formula returns the last cell whether value or formula cell containing no value

=ROW(OFFSET(A11,COUNTA(A:A)-2,0))

Many thanks

Ian
0
Hi Techs.  I am in a new role and am using Google Sheets.  I needed to make a template for my daily report. So I made a copy of the the sheet with its' tabs, deleted the data, saved that as my Template, and the original data was completely blown away.

How can I prevent this from happening again?

* I was able to get the entire deleted sheet back by using File > "Version History" > and then saving the last version I worked on.

Thank you,
Mark
0
Hi,

I have a very large database which I would like to divide into eight separate sheets. Reason being it is too large for the tasks I want to  perform.
For simplicity sake lets call the columns, Gender, Age, ID

The sort would be the following

1) Male, (age) <20, (id) <10
2) Male (age) <20, (id) >9
3) Male (age) >19,(id) <10
4) Male (age) >19, (id) >9
5) Female, (age) <20, (id) <10
6) Female (age) <20, (id) >9
7) Female (age) >19,(id) <10
8) Female (age) >19, (id) >9

The way I would normally do it takes forever.
If there is a much quicker way I'd be very appreciative.

Many Thanks

Ian
0
Hi,
I'd like a formula to select data where first letter in a name = U
example
                               formula
UABCD   46            =46
ABCD     32            = 0
ACDE     46            = 0
UACDE 50              -50

Many thanks

Ian
0
Greetings experts,

Here is a screenshot of the "Key-In Data" sheet I am trying to work with:
Capture.PNG
And here is my code:
function myFunction(){

var app = SpreadsheetApp;
 var activeSheet = app.getActiveSpreadsheet().getActiveSheet();
{var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("B26");
var emailAddress=emailRange.getValues();
{
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
ss.getRange("E2").setFormula("=B2+C2+D2");

 for (var i=2;i <= 2;i++) {
 var currentCell = activeSheet.getRange (i,5).getValue();
   if(currentCell < 4){
   var message="Product has reached a critical value "
   var subject = "Update on transfusion product (EMERGENCY!!)";}
   else if (currentCell > 6) { 
   var message="Product has reached a normal value"
   var subject = "Update on transfusion product";}
   else 
  {var message="Product has reached a minimum value"
   var subject = "Update on transfusion product";}
  
  MailApp.sendEmail(emailAddress,subject,message);
  }

}
}

}

Open in new window


What this code does is that it will send an email based on cell E2. The logic is completely fine here and it does not require any changes.

What I do need help with are:
1. Right now, the code is pointing to cell B26 for the email. I need the email in the code instead.
2. I also want to add cell E2's value at the back of the Email message. For example, "Product has reached a critical value of 3".
3. Every time the code is run, a time stamp should be generated at cell A2.
4. After the timestamp is added, I need row 2 of "Key-In Data" sheet to be added to the next available row in the "Summary Data" sheet.

Here is sample test environment:
https://docs.google.com/spreadsheets/d/1kA1CH2I0Rho_GcvO_PqsWIAuZMZlK4BFukP-TsUlmyY/edit?usp=sharing

Any help is much appreciated! :)
0
Experts,

I have a group of values on a spreadsheet "Sheet 3"  Column A  Rows 2 =15 that I have assigned to a name field called 'East'.  Is there a way to check column "A" on Sheet 1 equal to one of the values in my named field and assign "EAST" to column "B' on the respective row.

Microsoft Excel 2016
Named_Feild.xlsx
0
Hi,
 
I would like a formula to find year in string.
Please see attached and place formula in A2

Many thanks

Ian
Year-change.xlsx
0
I sell licenses for various different software vendors. I currently use spreadsheets and mail merge to email reminders for renewals. I could do with something to automate this. Anyone know of an off the shelf solution or something that needs minimal tweaking. ?
0

Spreadsheets

A spreadsheet is an interactive computer application program for organization, analysis and storage of data in tabular form. Spreadsheets developed as computerized simulations of paper accounting worksheets. The program operates on data represented as cells of an array, organized in rows and columns. Each cell of the array may contain either numeric or text data, or the results of formulas that automatically calculate and display a value based on the contents of other cells.

Top Experts In
Spreadsheets
<
Monthly
>