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.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hello all,

I am importing data from another sheet, but I need to filter so that only the last 60 days of data appear.

First, I have this formula:
=ARRAYFORMULA(LEFT(QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/[address]/edit#gid=","Form Responses 1!A:A"),"select Col1 where Col1 > date '2019-12-21'"),9))

How do I change this formula to display only the last 60 days, rather than using a hard coded date?
I tried and failed to replace the hard coded date with variations of date() and - 60.

Finally, I need to combine the above solution with:
=IMPORTRANGE("https://docs.google.com/spreadsheets/[address/edit#gid=","Form Responses 1!J:K")  -

I tried changing this to ...
=ARRAYFORMULA(LEFT(QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/[address]/edit#gid=","Form Responses 1!A:K"),"select Col1 where Col1 > date '2019-12-21'"),9)) but this resulted in just the first column 'A' (the date) appearing, ignoring the important content in columns J and K - the only columns (besides A) that I need.


Thank you
0
Hello all,

I am using IMPORTRANGE to bring in an array of values from another sheet. The problem is that some of the cells include text that I need to filter out.

=IMPORTRANGE("https://docs.google.com/spreadsheets/[removed]","Form Responses 1!D2:H20")
brings in some cells like: '8 (Great!)', but I need to remove the non numeric part.

I could make a separate column and use the formula: =VALUE(REGEXREPLACE(TO_TEXT(A3), "\D+", "")), but this will take up a lot more cells.

Is there a better way to display only the numeric values imported via IMPORTRANGE?


Thank you :)
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
I have a new SQL database on Azure. I can connect to it from SQL Server Management Studio. And I can connect to it from Excel via the Ribbon.
But I can't connect with the usual way I do with ADO.
I tried this connection string, as given by Azure portal. (you can try with my live credentials. No sensitive data at the moment)
"Server=tcp:reimagineexcel.database.windows.net,1433;Initial Catalog=ReimagineExcel;Persist Security Info=False;User ID=hiran@reimagineexcel;Password=XXXXXXXXXXXX;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

Open in new window


I have also tried this, from the usual connection string I use to connect to my other SQL Server hosting online:
"Provider=sqloledb; Data Source=tcp:reimagineexcel.database.windows.net,1433;Network Library=DBMSSOCN; Initial Catalog=ReimagineExcel;User ID=hiran@reimagineexcel;Password=XXXXXXXXXX;"

Open in new window


I tried recording a macro to see what I should use but Excel skips recording the connection details.

More info: I have set the firewall permissions on Azure to 0.0.0.0 to 255.255.255.255.

Thanks for any help.

(Also: if for any reason Azure is not suitable for my purpose of allowing many distributed Excel spreadsheets connect to an online SQL Server if there an alternative? Amazon Web Services?)
0
I need to merge a variable data multi page PDF doc.

The excel spreadsheet does not have all the merge info in one row.  

I am attaching a sample spreadsheet and sample result PDF.  

A few things to note -

1.) Each row in the excel spreadsheet is not necessarily a new form.   The "ID for 1095" controls whether it goes into the existing 1095 form or if it is a new form.  The Employee ID can also be used for this purpose.
2.)  The social security number should be changed to ***-**-5624, even though the spreadsheet has the full number.
3.)  In part 3, all the covered employees need to be filled in.  This is where the information is gathered from the spreadsheet based on employee ID.  Secondly, the selections from the spreadsheets for months covered should be converted from "Yes" to check boxes here.

I don't mind purchasing a tool/software to do this, if there is one out there which will simplify this process.  What complicated this is that the form is a PDF and not a word doc.

Any advice on how to tackle this would be greatly appreciated.  

Thanks.
Test-File.xlsx
f1095c.pdf
f1095c1.pdf
0
I found a product to do SMS Group Texting from a google spreadsheet. This looked like a perfect solution for me, but I cannot get this product to work

It did correct the number format when I pasted my list of numbers the number into the sheet.
https://gsuite.google.com/marketplace/app/text_gblaster_sms_texting/360232482012

This comes up on the side of the sheet also and looks potentially useful, but the links seem to point to a sheet that is blank and I don't see a Facebook page where I can ask questions or get any assistance. https://www.screencast.com/t/XhsTBvs1C7.

Assistance is greatly appreciated.
0
Good Evening Experts,

I am having an issue building out a spreadsheet to solve a tiered mileage equation. I have tried using IF statements, SUMPRODUCT and vlookup statements but not having much luck. I tried "JE McGimpsey's" variable rate equation, but didn't have success.Spreadsheet Example
The pricing structure is tired so that the first mile is billed at a higher rate, than the next ten miles are at a different rate, continuing so-on and so-on. I have included a mockup of what I am trying to accomplish with a few manually solved routes. I need to figure out a way to automate the calculations for A,B,C,D,E,F

Thanks for any help or insights you might have.
0
Paste a column of numbers into an SMS message.

If I want to send a column to a group of say five (5) different ten (10) digit numbers, My Android Galaxy Note 8 throws up the error that says "unable to add this recipient, recipient's number is not recognized" I've tried several separators, ; space and enter but no progress.

Please advise. Your assistance is greatly appreciated.
0
Greetings experts,

Let's say for an entire sheet, I need a message box to appear to confirm any edits for any cell that editing is attempted on. If "ok" is pressed, edits are saved and if "cancel" is pressed, edits are discarded.

Any help is much appreciated! :)
0
My goal is to create a Google document that is automatically populated based on user selections from a <Google form, spreadsheet, any Google G Suite product>.

Here's the use case:
I have a Business Requirements Document template, but users don't need every single section each time they use the template. What I'd like to offer them is a form/method for them to check the items they need and have a document populated with those sections vs. opening a template and deleting the sections they don't need. I've attached an example of my Business Requirements Document, and would like to keep the template formats in tact, too.
TEMPLATE----Measurement-Plan.docx
0
Hi,

Our company has very important spreadsheets created in Google Sheets that they would like to protect.

My employer wants the ability to password protect these Google Sheet spreadsheets.

Each individual sheet has sensitive information.

Google accounts should be secure but in case anyone got into it, they would like to know of a way to protect these spreadsheets for added security.

How would we do this?

Thanks,
Robbie
0
Using Google Sheets where column D contains a list of email addresses previously in use. In comes a new email containing basically the same list of emails, but potentially with some additions which I then add to the bottom of my sheet in column D.  What I want to do now is highlight all the newly pasted emails that are not in the original list above. The first list is D2:D226 and the new list is D228:D270.  Items in D228:D270 should be highlighted if they are duplicates so that I can remove them.

The instructions on this page https://stackoverflow.com/questions/21899516/how-to-highlight-cell-if-value-duplicate-in-same-column-for-google-spreadsheet say to do the following.

Select the whole column
Click Format
Click Conditional formatting
Click Add new rule
Set Format cells if to: Custom formula is:
Set value to: =countif(A:A,A1)>1
Set the formatting style.
Ensure the range applies to your column (e.g., A1:A100).
Anything written in the A1:A100 cells will be checked, and if there is a duplicate (occurs more than once) then it'll be coloured.
For locales using comma (,) as a decimal separator, the argument separator is most likely a semi-colon (;). That is, try: =countif(A:A;A1)>1, instead.

I don't fully understand the syntax to be used or how to implement it. Assistance is greatly appreciated.
0
I'm planning to use the export to Google CSV file to get contacts out of google for manipulating and then re-importing them.

The trouble I see currently is that the photos won't be likely to re-populate.

Does anyone have a tested solution for this?

Assistance is greatly appreciated.
0
Fill Google Spreadsheet with data from Google Contacts

My Google Contacts database is potentially one of the most valuable information resources that I have and I would like to be able to sort and filter it more efficiently.  The built-in options at contacts.google.com just don't do enough and exporting and importing is very old-school.

I need to be able to sort and filter them using a Google Spreadsheet instead of being relegated to the minimal options provided within the context of the Google Contacts page itself.  

One option might be to use the Google scripting area under the Tools menu item within the spreadsheet. Assistance is greatly appreciated.

I would greatly appreciate any assistance in resolving this challenge and I have begun a sheet below to give us a point of reference as I try to find a solution to implement here.

https://docs.google.com/spreadsheets/d/14OH97o7dSVa5eC4WXjwHKdzmsJEinQLrVldaqOF-MwE/edit#gid=1636004216
0
I need to calculate CAGr by our Fiscal Year (which starts in November) for balances for several different accounts based on the open (beginning) date of the account.

For example account A opened November 25, 2015, Account B opened June 2, 2009, Account C opened February 15, 2019. The Fiscal year begins in November of each year

Not sure if this is relevant, but the balances are already cumulative; i.e. the balance for the most current month is the sum total of all previous months/years.

If an account doesn't have a full year's history, I'd like it to be able to pro-rate CAGr based on the month we're currently in.

I have a table already set up for fiscal years, but it's in a different table than my accounts and balances and open dates. The tables are linked, so not sure if that even makes a difference.  

Ultimately, I need to be able to select an account based on a slicer or some other filter (I already have set up); it should then calculate the CAGr in a card or table visual based on the selected account.  Behind the scenes, it should also know the beginning dates of each account (which is used to calculate CAGr).    

Unfortunately, I can't send a sample file due to the confidentiality of the data, but hoping this is fairly intuitive the way I've explained it here. Thanks in advance for your assistance!
0
Office  365  says I have too many working assets when I upload an excel spreadsheet with
60 columns (basic math formulas).
And 20,0000 rows.
I am looking for maybe an alternative such as an excel style tool where it works with such big spreadsheets and must have the ability to collaborate with 5 people at once.
0
In radSpreadSheet how can I set a cell value by its name? meaning excel cell "B1" for example
0
I'm trying to read through our phone system log and increment a number in a Google sheet to track  how many calls we have for a particular hunt group "BState=Ringing" and then decrement the number when the call is finished "Deleted".  I would like to read a line from the log, check to see if the call is for "HuntGrp1", if "BState=Ringing" then copy unique identifier "16.12345.1" to an array, increment a cell in a google sheet.  Then I will have to check each matching "HuntGrp1" log line to see if that unique identifier is in the arrary and delete it from the array and decrement the cell in the google sheet when log line containing "Deleted" is encountered.  I have tried multiple ways to add elements to array/collection with  inconsistent results.  Here is what I have and some sudo code.

$RegexHG = "HuntGrp1\(304\)"
$UICollection = {$callid}.Invoke() #Collection for Unique Identifiers

Get-content "C:\Temp\TestData.txt" -Wait | where {$_ -match $RegexHG}| 
foreach {   
   If ($_ -match "BState=Ringing") {
    #increment counter Google Sheets
    #add unique identifier to arrary
   }
   Else {
   	#If unique identifer in array
   		If ($_ -match "Deleted") {
   			#Remove unique identifer from arrary
   			#decrement counter Google Sheets
   		}
   }
}

Open in new window



Sample Data
08:00:56 1299975656mS CMTARGET:     c0a800f40000a7bc 16.12345.1 11380 SIPTrunk Endpoint: LOOKUP ICR: DDI=1234567890 CGPN=0987654321@1.1.1.1  Matched Time profile: Name1 (Destination 

Open in new window

0
I have extracted data from our application into excel and one of the cells contains data laid out like this;

$250,000.00 - Building

$  35,000.00 - General Contents

Is there a way to extract the numbers and then add them to get a grand total in another cell?
0
I am using 2 spreadsheets.
1 is DataBasexls
the other is
PartLookup.xls

I want the PartLook up database to react this way:
In field A2, if I enter a part number, perhaps from a dropdown, and select it.
then I want field B2 and B3 to show the description and quantity.
those fields are also in DataBase.xls file.

How can I start this..
0
Hi,

We share a Google Sheets spreadsheet with our client.

Is there a way of finding out when the client viewed our shared spreadsheet and for how long?

Thanks,
Robbie
0
Hello all, and Happy Friday!!  
I've been trying to build a useful spreadsheet for a client in Google Sheets that requires three functions I am having difficulty with, and they are so linked together, I am afraid to post them as separate questions.  Here goes:
1) I would like to automatically export data from only one tab (Tab D) of a multi-tabbed daily spreadsheet into one separate new monthly summary spreadsheet. (Outcome: all data from tab 'D' from all (m-f) daily reports appears in a new single summary of that tab for the entire month. So my daily reports have tabs a, b, c, and D, but we get to see a whole months worth of 'tab D' in its own separate monthly report.)
2) In a separate tab in this new monthly summary report, rather than listing the litany of measurements we've made that month and have a clunky, crowded spreadsheet,   I would like to include a drop-down menu that indicates different criteria, and when that items is selected, that function is executed.  I've attached a small sample of what I mean. And lastly,
3) I would like a chart accompanying that selection to appear when selected, if possible.  So in the example I attached, when I click on 'Brand New Member - Did Not Work Out' my desired outcome is that the number of members who signed up for the gym but did not work out would be displayed (that data lives on another tab in this same report) AND that the chart/visual for that data ALSO appears when that item is selected from that drop-down menu...

0
Hello Experts Exchange,

I have a client that recently added an address lookup feature in a workbook to check for an address across multiple spreadsheets in the same workbook. The address information in the workbook goes from 2007-present, but from my observation it is only returning data for certain years.
The error is =IFERROR(INDEX($A$6:$C$3000,$J6,COLUMNS($J$5:J5)),"").

Thanks in advance for any assistance!
eesnip.PNG
0
Hi,

I have a Google Sheets spreadsheet shared with a few other people who have edit access.

I would like to hide one of the columns in this spreadsheet as the column is not needed but want to have the ability to unhide if it is ever needed.

1. Is there a way of hiding a column from all people who have access to the Google Sheet spreadsheet but where I am the only one who can unhide the column?
2. Is there a way of entering a column into a shared Google Sheets spreadsheet where I would be the only one who could see the column data?

Thanks,
Robbie
0
There is a hidden column L in the source file ( keno results) which is upsetting the formula when  I delete the column L  in the source.

I deleted the column L in the source file and now I am able to do a exact copy and paste into the manually entered excel file(destination file) and it pasted correctly into the cells c:v

but in doing so I lost the formula because the monitored numbers count did not get updated.

How do I save the formula, while at the same delete the hidden column L in the source file ?

I also type 20 numbers for the 31st March (just to test out)  in the source file and to see the if results get updated in the destination file , but it did not get updated. I highlighted row 122 and pulled the fill handle down to row 123 to populate the formula and the source file was requested and I clicked on the source file and after that the destination file did not get updated.

keno-reuslts.xlsx source excel file
manual-entered-CondFormatQ29141105.xlsm destination excel file
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.