0

I need to come up with the script for an import button on my google spreadsheet, but I'm a little out of my depth.

I have one Spreadsheet with two Sheets:

  • Purchase Order template (Sheet2 = 発注書)
  • Product Requirements data (Sheet3 = 朝食)

The goal is to populate the Purchase Order (Sheet2) from a row of Product Requirements data on Sheet3. The Product Requirements data includes a Purchase Order value.

What I'm trying to achieve is to import data from the second to the third tab in the following way:

Process:

  • Enter a Purchase Order number in Cell B2, Sheet2.
  • Manually start the macro (by click the "import" button) to copy data from Sheet3 to Sheet2.
  • Find the row on Sheet3 where the value in Column A equals value entered into the Purchase Order Cell B2. Then, copy values from that row on Sheet3 to the Purchase Order (Sheet2)
    • Column B - copy to Sheet2, Cell D16
    • Column C - copy to Sheet2, Cell C15
    • Column D - Copy to Sheet2, Cell D20
    • Column E - copy to Sheet2, Cell D21
    • Column G - copy to Sheet2, Cell D14

*Other columns do not need to be imported.

Basically, depending on the value I insert 発注書!B2 (Sheet2, B2), I can import different data from the 朝食 tab row. I apologize if it's not clear enough. I tried modifying the script based on an existing excel doc which used the formula but I failed.

This is a sample spreadsheet to illustrate what I'm trying to do: Sheet2 (朝食), and below Sheet3 (発注書) enter image description here enter image description here

4
  • Hi and welcome to WebApps. I think your locale is Japan and I regret that I neither speak Japanese nor understand the written language. In your question you explained that each of the cells on Sheet3 was to be copied to the Purchase order (Sheet2), and you gave three examples, Column B, C and D. However, Sheet3 has 9 (nine) columns (A-I), but you did not explain the location on Sheet2 for values in Columns E, F, G, H and I. Would you please edit your question to show where values in Columns E-I are to be copied on Sheet2.
    – Tedinoz
    Feb 22, 2022 at 1:45
  • Hi, thank you for the reply and sorry for the unclear question. I actually just had the idea of using vlookup and import range in the following formula: =VLOOKUP(B2, IMPORTRANGE("url","'朝食'!A3:I"),2,FALSE) , but I wonder if there is any better way to do it and if I better make it into a macro or leave like this.
    – Cristina
    Feb 22, 2022 at 2:04
  • Let's assume that you want to print the Purchase order. I think a series of VLOOKUP formula would be easiest, simplest way to do it. The formula is slightly different, but I'll show you what I mean.
    – Tedinoz
    Feb 22, 2022 at 2:25
  • Hi, thank you for the reply. Yes, printing is defnitely a requirement :D A series of VLOOKUP formula sounds good. I'd be really grateful to learn about the formula to do that.
    – Cristina
    Feb 22, 2022 at 2:33

1 Answer 1

0

Using VLOOKUP

  • Enter the purchase Order Number in Cell B2

  • Use this formula in the appropriate cell

=vlookup($B$2,sheet3!$A$3:$I$199,2,0)

  • $B$2 - absolute reference to the Purchase order number
  • sheet3!$A$3:$I$199- link to data on sheet3. The extra rows will allow for expansion of the data on Sheet3
  • 2 - index = 2 (= Column B)
  • `0' - looks for an exact match

The same formula can be used in many cells, just change the index number to return the appropriate column value from Sheet3.

Sheet2

Sheet2


Sheet3

sheet3

3
  • Thank you so much!! Just tried it out and worked perfectly.
    – Cristina
    Feb 23, 2022 at 23:29
  • Excellent. If this answer was helpful for you, then perhaps you might consider accepting it.
    – Tedinoz
    Feb 24, 2022 at 2:33
  • Done! (Sorry, it took me a while to figure how to do it. )
    – Cristina
    Feb 25, 2022 at 3:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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