Excel Formula

Ask for Help
1

Can anyone help me,

I have a page data like:
(PAGE 1)
Model 1 | Quantity
Year 18 | 3
Year 19 | 5
Year 20 | 1
Year 21 | 2

Model 2
Year 18 | 13
Year 19 | 15
Year 20 | 11
Year 21 | 12

…
…
And others model
In other sheet.

The formula in the cell will be

Year 18
Model 1 ='PAGE 1!A2
Model 2 ='PAGE 1!A6

It add 4 from the first cell (A2 to A6)

What is the formula to easy drag because the model like a lot.

2

Can you go into more details or include some screenshots? Do you mean you want to include all models in a Sheet where it would look like.

Model | Year | Quantity
Model 1 | Year 18 | 3
Model 1 | Year 19 | 5
…
Model 2 | Year 18 | 13
Model 2 | Year 19 | 15

3

This is my raw data:

image
image1366×768 86.2 KB

I want to summarize for example year 2019:
CASE : 0 ='PAGE 1!C14
CATERPILLAR:17 ='PAGE 1!C20
HITACHI:19 ='PAGE 1!C26

I need it to drag down automatic go
c14>c20>c26>c32>c38

Add 6 for each row

4

Hi @biha , just a few comments:

  1. As of writing, as far as I know you can use a Google Sheet or Glide Tables to store your raw data in tables, not Excel.
  2. The data in your spreadsheet should be organized in a specific way to be exploited in your app. Organizing your spreadsheet
  3. To “pull” a formula in Google Sheet, have you tried arrayformula ? Excel probably has the same or equivalent formula?
1 Like
5

I understand that.
For your comment number 1:
To pull from my excel to google sheet, my data must be organized.
So i need make some summarization .

1 Like
6

The screenshot from Excel is kind of irrelevant, because that doesn’t connect to Glide.
If you can show what your GoogleSheet looks like, it would be easier to provide some guidance/assistance.

2 Likes
7

I’d have to dig into it further to see what the excel equivalent is with the formulas, but I think you might be able to use something like INDIRECT or ROW to dynamically build a cell reference from another cell or from the row number. Also thinking you could maybe do something where you multiply a row number by 6 or add 6 for example to let the drag down auto increment properly…But as I look at your screenshot, I’m not sure how a drag down formula would work because you would have multiple years that you are trying to sum in the same column. Where is that summarized total supposed to be stored? Do you have the same number of equipment manufactures, or can that number change?

Unless I’m misunderstanding, I don’t think you want a formula to drag down. Instead you might want a formula in a single cell that adds up the totals for every nth row. Something like this:

1 Like