google sheets

  1. C

    Google sheets formula for values that are greater than or equal to but less than

    I need a formula for a sheet which will calculate the sum of values in another sheet between a range i specify. For example, find the sum of 'Sheet2 column A' where values in 'Sheet2 column B' are greater than or equal to 3.0 but less than 4. Sheet2 looks like the following <tbody> Total...
  2. S

    Batch Data Import to Google Sheets

    Hi, Thank you very much for taking out time and trying to help me out. Here is what I am stuck with : 1. I have a list of around 4000 companies 2. Each of those companies have three financial statements (two of them enclosed for reference) 3. Therefore I have around 12000 sheets to work with...
  3. S

    Excel table equivalent to tree diagram

    How do I create a 12-month table with several variables in Excel? Say I start with 5 possible X values and have two growth factors, each with 5 proposed values I want to test (Z1-5+Y1-5) through the end of the 12th month and see the product of each combination (125) like a large tree diagram would.
  4. J

    Power Query from Google Sheets results in #DIV/0

    Hi everyone, I'm new to this forum. I use Excel on a daily basis for work and have recently started playing around with Power Query for pulling in data from the internet. Haven't been able to find any info on this specific problem that I've been having. Google Sheets has a handy finance...
  5. A

    Add 1 hour to Timestamp with if statement

    I want to add 7 hours to timestamp if time is 01:00:00AM and add 1 hour if it's not 01:00:00AM. I tried - if(A2=TIMEVALUE("01:00:00AM"),A2+TIME(7,0,0),A2+TIME(1,0,0)) A2 = <style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>08/Jan/19 8:0:0AM 1...
  6. P

    Complex Sumproduct Formula Not Translating to Google Sheets

    I'm trying to convert a model that has a complex sumproduct formula: "=SUMPRODUCT($AX14:CB14,N(OFFSET($BX$4:DB$4,0,COLUMN(DB$4)-COLUMN($BX$4:DB$4))))" And once the column count gets to 31 (range of AX->CB...BX->DB), I get the following error: "SUMPRODUCT has mismatched range sizes. Expected...
  7. C

    Excel saved as a Google Sheet / Google Drive

    I'm wondering if having a more current version of Excel uploaded onto Google Drive will affect the functionality of what can be done? For example, if I have a file created in Excel 2016 and upload it to Google Drive, will I have more Excel features/functions than if the document was created in...
  8. D

    Assigning a value based on other cell data and certain criteria.

    Hi, I manage housekeepers in a hotel and I am trying to create a spreadsheet that helps me make room cleaning assignments based on certain criteria. The sample sheet shows the 3 columns of criteria for each room. Room Type, Room Condition, Stay-C/O (Stay over vs. Check out). I also want to...
  9. E

    Google Apps Script for Google Sheets Pulling Formulas from Master to Several Slave Sheets

    I need help with ONE FORMULA or ONE SMALL SIMPLE SCRIPT in Google Sheets to solve this spreadsheet problem. I have a master spreadsheet with all my formulas in a row. I want to have many slave spreadsheets pulling the formulas in via IMPORTRANGE. So if I need to change a formula I can change...
  10. T

    [Google Sheets] How to index/match multiple criteria across multiple sheets [data in same order]

    I have data from different campaigns in separate sheets [per market]. Actual data is five sheets but in the dummy data, I've only worked with two sheets. In a summary sheet, I want to be able to find the correct value for Sessions in column I of 'Email Sends' sheet. This should check for the...
  11. J

    Searchable drop down list as seen on google sheets (Data Validations)

    Hi Guys, How do you create searchable drop down list as seen on google sheets? I have excel 2016. I want to use the drop down to complete a column in a table. I know I have to use data validation. Thank you.
  12. C

    Google Sheets: Decimal to Fraction Script

    could anyone correct my function. tired of copying and pasting this formula over and over. Trying to get it to work in a script. cell D3 is an example of what id like to input. Thanks https://docs.google.com/spreadsheets/d/13Dr8cFYFE9xNTGSYyFbAY4_vKjANavSa7GcSd-6hRhk/edit?usp=sharing
  13. C

    Google Sheets: Script for changing background of cell color.

    I am very familiar with conditional formatting, however this situation presents a different kind of obstacle for me. I need a script that will change the cell to the left,right and below etc, a different color if a condition is met without being attached to a cell number designation(A1 etc...)...
  14. M

    Google Script App - Copy & Paste Loop

    I have a bunch of data in a Google Sheet and I’m trying to create a simple script that’ll loop and copy it over into the correct place in a second Google Sheet. DM SAMPLE SHEET (SOURCE) SM SAMPLE SHEET (DESTINATION) Rules for copy and paste: Loop through each row in the source sheet. If...
  15. A

    Auto update google sheet from Excel

    I have a google tracker which I updated everyday by uploading data from Excel. The process is cumbersome as many times the sheets/excel freezes. If there a VBA code/excel add-in that I can use to upload my data to google tracker from excel. The code should delete the old data on sheet and...
  16. W

    Google sheets charts grids questions

    Dear all, Kindly advise, why is it happening that the first two charts here have lost last few "X-asis" labels? In comparison with the other charts, where all the labels are present, and the same formulas and data ranges are used for X-axis...? Also, is there any way to switch the vertical...
  17. X

    Can I "lock" an Excel file from being uploaded to Google Sheets, or make it unusable/disable?

    Hello- Not sure this is the right spot for this one... I have issues with coworkers uploading an Excel file that I have password-protected into Google Sheets, which removes all of the protections I have created (sigh), and allows them to see hidden sheets, etc. I am wondering if there is some...
  18. D

    Conditional Formatting Help! Highlight matching cell in different sheet

    Hello all, Here is my challenge: I have a spreadsheet made up of 5 different sheets. Four of these sheets represent a different company (Company A, B, C, & D) and show their prices for a range of products at a range of quantities. I then have 1 sheet called “LOWEST PRICE” that pulls whatever...
  19. T

    Conditional Formatting

    Hello all! I'm hoping this will be a fairly easy fix, but I can't figure out how to get it to work. We assign tasks to our team, and everyone has their own tab. There is a "MASTER" tab that keeps track of the name of the task (A2), how much time was budgeted for the task (B2), and how long it...
  20. T

    Creating a mastersheet with synced sheets

    HI, I am trying to do something that I thought should be quite simple. I am using Google sheets, not excel, on a Mac in Chrome. I have 10 columns and multiple rows of data, which is my master data. In the same workbook, but on a separate sheet, (so not importrange) I want to reflect that data on...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top