I have a workbook that has information from a form, and I need to analize it but in another workbook, how can I create a pivote table in a new workbook2 but using data from de workbook1
I need a formula to reference an external workbook for to query a cell range for a certain entry to exist, and if that specific text is present, it will cross reference the a cell from a certain column in the same row and check if it is present in a column of the source sheet – if it does, it can populate with “Present”, and if it does not, populates with “Needs Added”.
In these example workbooks, I would like “Example Spreadsheet” Column B to house this formula. The Formulas will reference “Example Spreadsheet 2” cell range
$A$2:$A for “Larry’s”, where “Larry’s” is present, it will compare cell range
$D$2:$D in “Example Spreadsheet 2” to
$C$2:$C in “Example Spreadsheet”. If present in both, it will populate with “Present” and if the store number is missing from
$D$2:$D, it will populate with “Needs Added”.
The Links to the Example Spreadsheets are:
I have here a sample project, where columns A to C are in FORM RESPONSES TAB/SHEET and columns D and E are in RECORDS TAB/SHEET.
In the RECORDS I have a list of names, in alphabetical order, and a column for BATCHES(E9).
What I wish to have as an output is to AUTOMATICALLY bring/distribute the batches from RESPONSES!B4:B to the respective attendees in RECORD TAB (E10:E).
NOTE: The names are already available in the Google Form(Checkbox type), so the list of names in RECORD TAB are just the same as in the Google Form.
But the problem is, there are responses in C4:C that has multiple names in a single cell, and I don’t know what to do anymore. hehe. I have already tried the INDEX and MATCH formulas based on how I understood it as a total beginner in Google Sheet, but it only works on the first name.
I hope that someone could help me achieve the output that I wanted to achieve.
Thank you so much and may God bless everyone! 🙂
Here’s the screenshot of my sample project:
I saw a proposed solution here, but it didn’t work for me.
How to use arrayformula to find and get data from only a certain, dynamic set of columns?
=SUM(INDIRECT(ADDRESS(4, MATCH("BB", 3:3, 0), 4)&":"& SUBSTITUTE(ADDRESS(4, MATCH("BB", 3:3, 0), 4), "4", "")))
I am trying to do something similar but it’s not working (“Formula parse error”)
Differences from the original (above):
- I’m trying to do an average, not a sum.
- The data to compare and average are both on another Sheet.
I want to be able to average a column of data below a header from another Sheet,
if the header on that Sheet (‘Vends’!) matches the cell to the left on the current Sheet (‘AGG-Vends’!).
Eg. If the header on row 2 of Sheet Vends! matches A2 on Sheet ‘AGG-Vends’!,
I want to average all the values below that header (row 3 and so on,
with an indeterminate number of values to be added).
My headers on Vends! Sheet are on row 2, so I modified the row number from which to start searching, to row 3.
=AVERAGE(INDIRECT( ADDRESS(3, MATCH(A2, Vends!A2:2, 0), 4, TRUE, Vends!)&":"& SUBSTITUTE( ADDRESS(3, MATCH(A2, Vends!A2:2, 0), 4, TRUE, Vends!), "3", "")))
My formula is in B2 on “AGG-Vends’! Sheet (highlighted Blue).
looking for a solution for upload a excel sheet to spring boot. it has process that excel sheet and sav e the data into sql database
A custom formula for 5 possible colors, by day is used to highlight entries by DayOfWeek
Is it possible to use a format like A2:A instead of manually editing it as the sheet grows bigger?
Formula works fine, just don’t want to be editing it continuously
Example for rows A2 to A641 – can this be $A$2:$A ?
Suppose I have a Google sheet with each row corresponding to a film, and columns corresponding to Title, Director, Nation, Year, Cast, Plot etc. Having sorted and filtered the sheet in some way of interest, I would like to be able to produce a Google document more or less of the form:
(Title from Row 1) by (Director from Row 1) with (Cast from Row 1) ((Nation from Row 1), (Year from Row 1)) – (Plot from Row 1)
(Title from Row 2) by (Director from Row 2) with (Cast from Row 2) ((Nation from Row 2), (Year from Row 2)) – (Plot from Row 2)
and so on.
That is, I’d need to simply reformat some contents of the sheet in a series of text paragraphs, with some minimal formatting (italics and boldface, say), in an automated way.
Is this feasible?
I am working with an Excel add-in that deletes and recreates a data sheet when you refresh with data from a live database, in such a way that references to the data sheet do not get broken. I am using the formula COUNT(‘DataSheet’!A:A) to count how many rows of data there are minus the column header row. If the data is not refreshed will recalculates almost instantly, but when the data is refreshed using the add in the count slowly increases until the formula has finished calculating. I need to use other similar formulas which are slowing down the workbook significantly.
I can think of workarounds, but I need to know what is causing the difference in behavior when COUNT recalculates normally, and recalculates when the data is refreshed. At the moment the data is the same number of rows before and after refresh and the formatted range is not longer than the data.
I have a Sheet where I have all of my data entered. I have been able to filter the information to other sheets no issues when only searching in one column. I am having issues when trying to search 2 columns at the same time.
On the Source Page, I have my date listed under the following headers: Player Name (A), OVR (B), Series (C), Primary (D), Secondary (E), Bats (F), Throws (G), How To Acquire (H), Team of Card (I) & Teams Played For (J).
For most sheets, I have been able to use the following formulae:
My issue comes from when I try to search both Primary and Secondary at the same time. I was able to use a query that searched both columns but am unable to do using SEARCH.
I am trying to correct the number/expression of room numbers that are entered by users with the following:
1- If the number contains ( / or or _ ), it will be replaced with “-“.
2- If the number contains “iso” or “Bed”, it should be removed or replaced with “”.
The sample sheet is https://docs.google.com/spreadsheets/d/1U-daD0CEPRLoCP7xCLT9bSMK9yN3ll8QQQfNk7-OZDs/edit#gid=1418057437.
Column A is to be entered by a user.
Column B is the result of arrayformula. The arrayformula is in B3 Cell. I couldn’t add more than two regular expressions. Like,
("|_|/").. what works is two only
It seems also complicated using two arrayformula functions in the same line.