How to solve the issue of my query results of in my sheet?

I have this sheet that I enter all data of patients in (Data_Entry) tab and I want these data to be sorted by Room number and bed number in the form of 00-00. For example Room 4 bed2 (4-2) and so on. I added a helper column L that modify the room/bed number in a way I remove the “-” and I multiply by 10 if the number entered has no “-” because some rooms are only one bedded room. Then I query all the data in All_patients Tab to be sorted first by unit(Column I), then by ward(Column B), then by modified Room&Bed (helper Column L). Because sorting by Column C (actual numbers like 4-3) doesn’t result in correct ascending numbers.

But I had a problem at the results in All_Patients tab that the original helper column L(Data_Entry) which is Column K in the results tab(All_Patients) doesn’t show all the values of the numbers.So, that resulted in an incorrect ascending order by room& bed number.

=ArrayFormula(IFs(REGEXMATCH(C5:C, "-"), SUBSTITUTE(C5:C,"-",""), C5:C<>"-",C5:C*10)) this formula is in L5 of (Data_ENtry)tab that modifies the room/bed numbers in actual numbers that can be sorted if needed.

=query(Data_Entry!B5:L,"select B,C,D,E,H,F,G,K,I,L where F is not null AND B<>'Ward' order by I,B,L",0) this formula is in B5 of (All_Patients) tab that brings all entry data and sort them by unit,ward,room.

Probably there is a smarter way of sorting room/bed number

How do I specify a consistent interval for a date-range axis in a Google Sheet Chart

I have a Google Sheet with a set of deposits and withdrawls and the running balance. I have added a chart to the sheet, so I can visualize what the balance has been over time and when deposits or withdrawls have been made.

Static Copy:


The production copy of the sheet is part of a much larger workbook of financial information. Normally, the data in this particular sheet comes from an import from another sheet, with the following syntax:

    ), 1, TRUE
  ), ""

…so the number of rows and the total date range is not known until the user selects a Bucket to view. The data range for the chart is set to C2:C1000,E2:E1000,F2:F1000 in the production copy of the sheet, to account for Buckets with a much longer history of transactions.

I would like the X axis to be formatted with a consistent interval from the first date to the last. I want the graph to show deposits/withdrawls on the dates they happened, but I want the balance to show a proper representation of the available balance over time.


The best way I can explain it is to provide a screenshot comparison of how Google Sheets spaces out the dates vs. Microsoft Excel. The most obvious difference is between 11/9/2020 and 12/9/2020. You can see that Excel properly condenses that period and Google Sheets seems to treat each data point as the same horizontal distance as the previous/next one.

Comparison of Google Sheets to Microsoft Excel

Microsoft Excel has the following options when formatting the axis:
enter image description here

How can I tell Google Sheets to do the same thing?

How to sum a range columns from another google sheet

I get this daily data consumption spreadsheet report from a vendor that looks like this

userid    feb1    feb2    feb3     .    feb29 
u1        100     34      23       .     4
u2        0       24      21             62
u3        300     25      5              1
u4        50      5       6      
un        23      52      3        .     42

where n is my total number of users.

What I care about is simply tracking the daily consumption of all users.. so my final sheet should look like this

date        daily consumption
feb1        14,971     
feb2        6,898       
feb3        10,666     

feb29        10,543     

Currently I’m doing this by writing this in each line in my final sheet, for example to get the 14,971 for feb1 I’m putting


Naturally this is very manual and slow work. I want to know how to do this using a single formula or pivot table etc. I tried using array formulas, queries, pivot tables but I keep on getting stuck. Any suggestions?

formulas – Combine multiple IF statement/condition into one google sheet column

How can I achieve in google sheet so that the column will auto populate the correct “vendor” based on the selection on “model” and “size”. Refer below:

enter image description here

There will be 2 different vendor based on the model and size, which user can made selection on B7 to B9
The formula in B8 is =IF(AND(B7=”S”,B9<>3),”A1″,”B1″)

The question is how can I combine all the possible IF condition into column B8

IF(AND(B7=”S”,B9<>3),”A1″,”B1″) + IF(AND(B7=”E”,B9<>5),”A2″,”B2″) + IF(AND(B7=”X”,B9<>7),”A3″,”B3″) + IF(AND(B7=”Y”,B9<>9),”A4″,”B4″)

Looking forward from the reply and really appreciated.

Thank you

Google Sheets link to cell in same sheet not working in IOS

I am working on a shopping list spreadsheet and I want to have links near the top of the spreadsheet to quickly jump the main category sections or store names (e.g. Frozen, Bread, Dairy or Walmart, Costco, Aldi…). I have many rows of items and trying to scroll through them to find what I want will be uber cumbersome without some sort of ability to quickly get around using links. I can get it to work on my PC by right clicking on the cell I want to link to, click on “Get link to this cell”, then going to the cell where I want to put the hyperlink and ctrl+K where I paste in that link with the name to display. In iPhone, when I click on the link cell in the updated spreadsheet, a black scroll menu pops up and I thought tapping on “Open link” would work, but it doesn’t.

Need help with google sheets, sending info from one sheet to another, but allowing the second sheet to be modifiable!

Guys for example I have this master sheet, which has one specific column that I want to filter by. for example it has different job titles, and I want that to be on another sheet so I can create “shortlists” for new applicants.

However, I would need data on shortlists to be permanent and not movable, so that changes in the master sheet don’t affect it!