0

I have a sheet where I need to add a number from another worksheet, but it isn't always in the same cell.

Let me explain: I need to add a formula to access a different Sheets file, search for the words "MONTHLY BUDGET: $2000" ("2000" will change as I will have various different sheets) and add to the cell just the number 2000.

I got most of this done by using the formula =VALUE(REGEXREPLACE(VLOOKUP("MONTHLY BUDGET: $*",J2:J100,1,FALSE),"[^[:digit:]]", "")) but this only works if I have the MONTHLY BUDGET information on the same sheet. How can I do this lookup in a different file?

I managed to import the other file information using =IMPORTRANGE(*FILEURL*, "SheetName!A2:A100") but this imports all the rows, since the MONTHLY BUDGTE will sometimes be in row 10, and other times, it may be in row 90. What I don't want to do is have to import 100 rows into my file for each client, just to look for the budget. Any way to do this without importing the entire information?

PS: I cannot edit the file I am importing from.

All I need to do is lookup a speficic text (MONTHLY BUDGET) in another file and import only the cell that contains this text. Can this be done?

Daniel
  • 3
  • 1
  • UPDATE: I managed to do almost what I want with the formula `=query(IMPORTRANGE(D2, "Month in progress!A:B"), "select Col1 where Col1 like 'MONTHLY BUDGET: $%'")` Now, is there any way to combine this with `=VALUE(REGEXREPLACE(VLOOKUP("MONTHLY BUDGET: $*",J2:J100,1,FALSE),"[^[:digit:]]", ""))` so that I can filter out the "MONTHLY BUDGET: $" text and leave just the numerical value directly, without having to do it on another cell? – Daniel Dec 15 '22 at 11:05
  • I actually got it. I am not sure it's the best way to do it, but it works exactly as I wanted it. I will leave my result here in case anyone else needs it in the future. This is the final formula: `=VALUE(REGEXREPLACE(VLOOKUP("MONTHLY BUDGET: $*",query(IMPORTRANGE(D6, "Month in progress!A:B"), "select Col1 where Col1 like 'MONTHLY BUDGET: $%'"),1,FALSE),"[^[:digit:]]", ""))` – Daniel Dec 15 '22 at 11:13
  • share a copy / sample of your sheet with an example of the desired output – player0 Dec 15 '22 at 17:24

0 Answers0