2

I'd like to populate Columns C and D in an entirely new spreadsheet, where Column D still auto-populates the cells just as it does in the original/first sheet (as it does in the photo attached inside this link)

I've tried the formula

 =IMPORTRANGE("1jD9MgFRnIi-44GaqsS5XAdhcV-Vg36eXnAXzkFh5xnM","'Form Responses 1'!C2:C")

in the new spreadsheet and it populates C, however, I only want it to show D IF/WHEN someone types their Username (from C in the original sheet) into Cell B3 in the new sheet (see image 2) ... If that makes sense.

P.s. I also only want two rows and two cells in the "new spreadsheet" (also see image 2). One Column for the search box (cell B3) where they type their username, and the other for the content in D (cell C3) that corresponds to that username in C.

i.e. D is autofilled when C is typed in

enter image description here

enter image description here

4
  • Is your screenshot of sheet#2 (immediately above), an example of a successful outcome. If not, please include an example of how you want your screen to appear. On a tangent, have you tried using VLOOKUP in conjunction with IMPORTRANGE.
    – Tedinoz
    May 17 '20 at 1:59
  • Yes and no. That formula in C3 second image literally just pulls the info from the other sheet and that's not the way I want it. I want it to autofill when someone types their username in B3 in the second image. B3 would be used as a search bar moreorless
    – Kedar
    May 17 '20 at 2:32
  • Yes, but auto-fill with what? Please provide an example of a successful outcome.
    – Tedinoz
    May 17 '20 at 2:46
  • In the first image/sheet, each cell in Column D autofills the value of C where the value in A is equals to the value in E. The formula used is also shown. So I want that, in the separate sheet, only if they type their username from C, would the corresponding value from D show up. (B3 in the second sheet would be like a search bar and C3 the corresponding result)
    – Kedar
    May 17 '20 at 3:01
3

You can try a QUERY formula like:

=QUERY(IMPORTRANGE("1jD9MgFRnIi-44GaqsS5XAdhcV-Vg36eXnAXzkFh5xnM","'Form Responses 1'!C2:D"), 
       "select Col2 where Col1='"& B3&"'")

The formula will import column C form the original sheet and display the value/name from it ONLY WHEN it matches cell B3 in the new spreadsheet (where they type their name).

Of course you can change the ranges to the desired ones.
One thing to remember about the query is that when using IMPORTRANGE in a QUERY you need to use Col1, Col2, etc (where Col1 is the 1st column in range), instead of the column letters.

You could also use a VLOOKUP formula like:

=VLOOKUP(B3,IMPORTRANGE("1jD9MgFRnIi-44GaqsS5XAdhcV-Vg36eXnAXzkFh5xnM","'Form Responses 1'!C2:D"),2,0)

The logic remains the same.


(Please do share a test sheet so you can be easier helped, It is all in the air without one)

5
  • Please try formatting your questions correctly. They are very hard to read being un-formatted. I mean you should know how by now. After all this is your 3rd question on the site :) May 17 '20 at 3:45
  • Please read how to and share a test sheet so as you can be easier helped. May 17 '20 at 3:51
  • First Sheet Share: docs.google.com/spreadsheets/d/… Second sheet Share: docs.google.com/spreadsheets/d/… I think I shared it correctly. The formula in your answer populates the same thing in both columns, however, if you look at row 16 in the first image I actually would like "@rifwiojrfwl" to show up automatically in Cell C3 in the Second Sheet when "@sjkfnjklsnjnw" is typed in Cell B3
    – Kedar
    May 17 '20 at 4:15
  • In the First Sheet, Column D would show who the person in Column C has to pay/gift. So, instead of having to see the first sheet, I want them to be able to type their username in the second sheet (which should always only have those two rows) and see who they would have to pay. (e.g. in the first sheet, if you look at row 16, "@sjkfnjklsnjnw" would have to pay/gift "@rifwiojrfwl") so, I want that, if "@sjkfnjklsnjnw" types his/her username in Cell B3 in the Second sheet, it shows in cell C3 that (s)he has to pay/gift "@rifwiojrfwl"
    – Kedar
    May 17 '20 at 4:27
  • You can still use the same query. Please see the updated formula and also an alternative. Also, please edit your question and add your last comment so others can understand it as well. May 17 '20 at 5:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.