CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Puggy
Puggy

Reputation: 107

How to grab a specific cell from reference from another google workbook?

These are the steps I have taken so far:

I have 2 workbooks, A and B

in workbook A I've used importrange

=importrange("website",J1&"!d9")

This allows me to import a specific sheet and a specific cell from workbook B. So I know this formula works.

Cell J1 in workbook A has a formula to dynamically give me workbook B's sheet reference but I hardcoded cell reference "D9".

I then tried to figure out how I can lookup the cell reference so after googling I found a formula that works but I need to use it inside workbook B.

=cell("address",index(A4:H13,match("Room - BS Harvest",A4:A13,0),match("10.31.21",A4:H4,0),4))

2 issues here. 1: it returns a cell reference with $ signs. So for example $D$9. I don't think it should be a problem because I tried hardcoding it in workbook A and it still returns what I need it to return:

=importrange("website",J1&"!$d$9")

2: My main issue is how I can combine importrange and the second formula (something like below) so I can lookup the cell I need from workbook B and return it to workbook A.

=importrange("website",J1&"!"& cell("address",index(A4:H13,match("Room - BS Harvest",A4:A13,0),match("10.31.21",A4:H4,0),4)))

But so far nothing has worked. I'm not sure if this is the best method or not.

My main goal is to be able to lookup (similar to index match or vlookup) a specific cell in a different workbook (workbook B) and return it to workbook A.

Upvotes: 2

Views: 100

Answers (1)

NightEye
NightEye

Reputation: 11184

I've created my sample data based on what your sample is, and this is what I have got.

Workbook B:

B

Formula:

=index(importrange(I1, J1&"!A4:H13"), 
match("Room - BS Harvest", index(importrange(I1, J1&"!A4:A13"))), 
match("10.31.21", index(importrange(I1, J1&"!A4:H4"))))

Where:

  • I1 = link of the website
  • J1 = sheet name to be used

Steps:

  1. Get the column and rows of the matching values using match and index
  2. Fetch the whole range where the data resides
  3. Use the data we got from steps 1 and 2 inside an index function to get the value.

Output:

output

Upvotes: 1

Related Questions