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.

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
Hi,
I'd like a formula to covert the below time format to seconds
Many thanks
Ian


17:23:06
0
Hi,

I would like a formula to replicate from a certain cell in a series.
Please see attached.
I have placed the correct outcomes in column F which refer to column A (yellow)
Please use column G for the formula

Many thanks

Ian
cell-value-formula.xlsx
0
Hi,
The below formula finds bottom row.
Can someone please help me modify it to ignore formula cells.
I only want last cell that contains a value.

=ROW(OFFSET(A9,COUNTA(A:A)-1,0))

Many thanks

Ian
0
My background is as a computer programmer specifically using access databases and VB for apps. My new job requires me to work with spreadsheets in excel. I understand the fundamentals in regards to creating tables from a master worksheet in excel and manipulating the information, vlookups, etc. Part of my job will be updating and adding information onto the master spreadsheet from various tables created by various department. I have not worked with the master spreadsheet yet but understand that there are various macros and formulas embedded in the worksheet. My concern is that I know when adding or updating information via rows or cells that this can have an effect on the entire worksheet. I am maticulas and do not want any mistakes. How would you recommend the checking process prior to entering on a production worksheet? I understand checking for duplicates on the table, checking formating in table cells to match the master sheet, etc. The master sheet has a large volume of data and I don't want to make a mistake by updating one cell and affecting another. Thanks for taking the time to review and offering suggestions on a checklist to adhere to accuracy.
0
I want to export from an Outlook public contacts folder to a spread sheet or database were i can apply bulk changes and then import back into Outlook contacts. CodeTwo Outlook Export does a great job on the export to CSV including categories, created date, modified date, modified by, etc.  Outlook's native export does most but not all exporting to CSV.  I tried to load Contact Genie, but it is no longer available.   I'm using Office 365 and Exchange Online.  Does anybody know of a program that properly handles export and import of all the Outlook fields?
0
Hi,
I have multiple spreadsheets open at the same time sometimes 4-5.
Can anyone advise best specs for monitors for this purpose.
giving clearest crisp text.
Will 4K improve text quality ?
I will use a 3 monitor setup and will NOT use for gaming.
Everything on the net nowadays relates to gaming !!
Thanks
Ian
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.