1

Spreadsheet I have a sheet I am constantly adding new items on. The current formula in column E, takes the latest price I am paying for the item.

I want to have this formula already built into any new rows added. Here is how it currently looks: Spreadsheet

I have not done any script before, but it looks like this is possible using an array formula.

Where am I going wrong?

Cwright
  • 29
  • 8
  • Hi, would it be possible to share a copy of your spreadsheet so we have some data to work with? – JPV Nov 30 '18 at 14:30

1 Answers1

0

Clear out all contents of column E. Then enter in E2

={"Current"; ArrayFormula(iferror(regexextract(trim(transpose(query(transpose(F3:BQ),,rows(A3:A)))),"[^\s]+$")+0))}

and see if that works?

JPV
  • 26,499
  • 4
  • 33
  • 48
  • Wow, that works great! Only issue is using regextract, I am unable to do vlookup on another workbook referencing column E Currently the formula I had been using was: `=iferror(VLOOKUP(A3,IMPORTRANGE("1Mu-mK3lxfRmK3_wQ4m8C_mqVY0BJOzWX4Uo01d-YZ7U","'Protein'!a3:500"),5,0),"")` It has no problem pulling words (E2=Current), just not the prices. – Cwright Dec 03 '18 at 15:58
  • Also tried Query: `=query(IMPORTRANGE("1Mu-mK3lxfRmK3_wQ4m8C_mqVY0BJOzWX4Uo01d-YZ7U","'Protein'!A3:E"),"select Col5")` And result comes back blank same as vlookup – Cwright Dec 03 '18 at 16:36
  • Check the formatting. If A3 is a number and the vlookuprange is text, convert A3 to text by adding A3&"". If would help if you could share your spreadsheet (or a mock-up). – JPV Dec 03 '18 at 17:14
  • This is the master sheet, that all other sheets refer to and has the pricing in column E:(https://docs.google.com/spreadsheets/d/1yoWTQoFFjSOgB2ewugfF_ga-tm_OS-s4JtQVxIX_vTs/edit?usp=sharing) This is the sheet that needs to pull the price from column E: (https://docs.google.com/spreadsheets/d/1nvw6wTJcwCXJbDJjA7D7tIPaM6m5CLicuAuceNN749k/edit?usp=sharing) – Cwright Dec 03 '18 at 17:32
  • Can you allow editing rights so that I try some formula? – JPV Dec 04 '18 at 07:58
  • Clear out column E of sheet 'Protein' then enter in E2 ={"¨Price"; ARRAYFORMULA(IF(LEN(A3:A), VLOOKUP(A3:A,IMPORTRANGE("1Mu-mK3lxfRmK3_wQ4m8C_mqVY0BJOzWX4Uo01d-YZ7U","'Protein'!A3:500"),5,0),))} – JPV Dec 04 '18 at 17:56
  • I could not enter it myself (and test it) because of protected ranges in the sheet. – JPV Dec 04 '18 at 17:56
  • I unlocked it. But all the formulas come back with blank results as if it is not recognizing the cells in column E as numbers. – Cwright Dec 04 '18 at 19:03
  • Is it a VALUE that needs to be added? But everything is coming back N/A – Cwright Dec 04 '18 at 19:08
  • Please remove the range protection in column E. – JPV Dec 04 '18 at 19:27
  • I just saw that column was locked. Everything is now unlocked – Cwright Dec 05 '18 at 00:43