index/match simple question

and8eye · Mar 16, 2018

i mean, in sheet1 i have this:

in sheet2:

in sheet3:

my question is:

how can i use index/match across multiple sheets (even when new sheet is created, possible?) at sheet1 A1 (i have thousand lines in B column in reality)?

thank you all

and8eye · Mar 18, 2018

Aladin Akyurek said:
There is indeed no sample in that thread, just picures.

since i couldnt edit #1 post of that thread, and seemed like there were an answer posted by someone else, please just ignore it, i dont want to bother you too much

btw, can you please tell me how to pull data from it

and8eye · Mar 18, 2018

TotallyConfused said:
 Hello And8eye

I’m not totally familiar with the ‘Index/Match’ commands, in fact I’m a relative newcomer to Excel, so what I’m about to suggest may not be possible at all. I was wondering instead of you specifying a sheet name in your formula, would it be possible to name the range of data. Then in the formula, instead of referring to a specific sheet, refer to that range name. I would think that doing this, it wouldn’t matter how many sheets were added, the range name and therefor the data location, would stay the same, but as I said above, I’m new to Excel.

If this won’t work, please let me know. I’m trying to learn Excel.

thank you much, i dont know sh*t too haha

and8eye · Mar 18, 2018

Aladin Akyurek said:
Put all of the data of debt1, debt2, and debt3 in a single sheet called debt in the "data" workbook. This would allow to do the processing in a summary sheet in a different workbook.

okay maybe i can, please tell me how to "pull all of the data"

thank you

Aladin Akyurek · Mar 18, 2018

and8eye said:
okay maybe i can, please tell me how to "pull all of the data"

thank you

Google Sheets does not have 3d calculation as Excel does.

One way out might be the following.

1. Combine debt1, debt2, and debt3 into a single sheet, say, debt, using the Combine Sheets add-on.

2. From within the summary sheet you can run a SUMPRODUCT formula which gets the relevan ranges from debt by calls to the IMPORTRANGE function:

month
people who owe me jan feb mar
andy 0 1234 1000
bee 4500 0 0
crystal 0 3001 0
iris 0 0 0
max 0 0 0
chloe 1234 0 0
kin 0 0 0

In B3 enter, copy across, and down:

=SUMPRODUCT(IMPORTRANGE("1AoTltgLusjGzqbFAONw321BzaKl2WT-fuXSvHxgLN50","debt!A:A"),--(IMPORTRANGE("1AoTltgLusjGzqbFAONw321BzaKl2WT-fuXSvHxgLN50","debt!B:B")=$A3),--(TEXT(IMPORTRANGE("1AoTltgLusjGzqbFAONw321BzaKl2WT-fuXSvHxgLN50","debt!C:C"),"mmm")=B$2))

You need to read on IMPORTRANGE and Combine Sheets.

and8eye · Mar 19, 2018

Aladin Akyurek said:
Google Sheets does not have 3d calculation as Excel does.

One way out might be the following.

1. Combine debt1, debt2, and debt3 into a single sheet, say, debt, using the Combine Sheets add-on.

2. From within the summary sheet you can run a SUMPRODUCT formula which gets the relevan ranges from debt by calls to the IMPORTRANGE function:

month
people who owe me jan feb mar
andy 0 1234 1000
bee 4500 0 0
crystal 0 3001 0
iris 0 0 0
max 0 0 0
chloe 1234 0 0
kin 0 0 0

In B3 enter, copy across, and down:

=SUMPRODUCT(IMPORTRANGE("1AoTltgLusjGzqbFAONw321BzaKl2WT-fuXSvHxgLN50","debt!A:A"),--(IMPORTRANGE("1AoTltgLusjGzqbFAONw321BzaKl2WT-fuXSvHxgLN50","debt!B:B")=$A3),--(TEXT(IMPORTRANGE("1AoTltgLusjGzqbFAONw321BzaKl2WT-fuXSvHxgLN50","debt!C:C"),"mmm")=B$2))

You need to read on IMPORTRANGE and Combine Sheets.

thank you very very much :x

Aladin Akyurek · Mar 19, 2018

and8eye said:
thank you very very much :x

You are welcome.

index/match simple question

and8eye

Board Regular

and8eye

Board Regular

Excel Facts

and8eye

Board Regular

and8eye

Board Regular

Aladin Akyurek

MrExcel MVP

and8eye

Board Regular

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

index/match simple question

and8eye

Board Regular

and8eye

Board Regular

Excel Facts

and8eye

Board Regular

and8eye

Board Regular

Aladin Akyurek

MrExcel MVP

and8eye

Board Regular

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock