1

I have a spreadsheet I have created with multiple tabs for users to input data on Wednesday of each week. I have a cover worksheet with an IMPORTRANGE formula to pull the data from the users sheets so it can be summarised.

Within each users sheets there are a list of tasks to be completed within a week range (Tues-Mon). Each week has been set in every 2nd column (A,C,E,G...) so the formula looks like:

Is there a way to add in a statement that checks the current date and can modify the formula to change A1:A11 to C1:C11 (E1:E11, G1:G11...) and so on each week?

3

2 Answers 2

0

The second argument to importrange() is an A1 style range reference as a text string, so you can choose which columns to import by using string manipulation to modify the text string.

Another way to do the same is to wrap the import function in filter() to choose which columns to include.

Implementation details depend on the particulars of your data and its headers.

2
  • so you can choose which columns to import by using string manipulation to modify the text string & to choose which columns to include. But isn't this is heart of the OP's question? How exactly does one use string manipulation to define the text string/address?
    – Tedinoz
    Sep 9, 2022 at 0:50
  • Your answer shows one way to do that assuming that column locations remain fixed. The filter() approach would probably offer more flexibility and be easier to maintain, but implementation details depend on the particulars of the data and its headers, which have not been given. Sep 9, 2022 at 5:29
0

The following explains a technique that will enable you to construct an IMPORTRANGE() formula using a dynamic range address.

  • The first example (long-form) shows every step in the calculation.
    • the key is the use of WEEKNUM() to identify the relevant week, and then adjust that against the column numbers.
    • this approach also coverts the column number to an alpha character in order to construct the cell address.
  • The second example is a compact formula using the principles/logic of the long-form method.

EXAMPLE#1 - long-form

long form

SAMPLE DATA (Sheet2)

sample data


EXAMPLE#2 - short-form

  • Enter the url in Cell B1.
  • Enter the sheet names in Row2
  • Enter this formula in Row 3 (cell B3, and copy to C3, D3, etc) =importrange($B$1,B2&"!"&regexextract(address(1,(((weeknum(today(),12))-(weeknum("2/8/22",12))+1)*2)-1),"[A-Z]+")&1&":"&regexextract(address(1,(((weeknum(today(),12))-(weeknum("2/8/22",12))+1)*2)-1),"[A-Z]+")&11)

short form

Your Answer

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

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