make this formula dragable

beekerdale

New Member
Joined
Oct 7, 2019
Messages
8
This formula works good for me for referencing another sheet, but I am looking to increase the size of the sheet to the right,
And so-I am wanting this formula to to reference R100, R101, R102, R103, R104 "and so on" as I drag it to the right.
Any suggestions?

=arrayformula((IMPORTRANGE("1mRD1xdG5YWOeYPJXPrc6xvuMgha54sbtiV-5imIdbC4","TimeSheet!r100")))
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,008
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
if this formula is in ColumnA then

VBA Code:
=arrayformula((IMPORTRANGE("1mRD1xdG5YWOeYPJXPrc6xvuMgha54sbtiV-5imIdbC4",INDEX(TimeSheet!R100:R120,COLUMNS($A$1:A1),1))))
And then Drag it
 

beekerdale

New Member
Joined
Oct 7, 2019
Messages
8
Thanks for the help, hum,, it says "unresolved TimeSheet name"
I think we are getting closer though,
 

beekerdale

New Member
Joined
Oct 7, 2019
Messages
8
I have updated the formula to this one below,,
I am working on the formula located in C3, And the reference on the other sheet will be from aj43 and dragged to aj51

=arrayformula((IMPORTRANGE("1mRD1xdG5YWOeYPJXPrc6xvuMgha54sbtiV-5imIdbC4",INDEX(TimeSheet!R43:R51,COLUMNS($C$3:C3),1))))

-this formula says "unresolved TimeSheet name"
Thanks for your thoughts, Dale
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,008
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

What about this:
Excel Formula:
 =arrayformula((IMPORTRANGE("1mRD1xdG5YWOeYPJXPrc6xvuMgha54sbtiV-5imIdbC4", Address(Choose(Columns($C$3:C3),43,44,45,46,47,48,49,50,51),18,4,TimeSheet))))
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,008
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Above formula is for R column, For AJ column Change 18 to 36
Excel Formula:
 =arrayformula((IMPORTRANGE("1mRD1xdG5YWOeYPJXPrc6xvuMgha54sbtiV-5imIdbC4", Address(Choose(Columns($C$3:C3),43,44,45,46,47,48,49,50,51),36,4,TimeSheet))))
 
Solution

beekerdale

New Member
Joined
Oct 7, 2019
Messages
8
Above formula is for R column, For AJ column Change 18 to 36
Excel Formula:
 =arrayformula((IMPORTRANGE("1mRD1xdG5YWOeYPJXPrc6xvuMgha54sbtiV-5imIdbC4", Address(Choose(Columns($C$3:C3),43,44,45,46,47,48,49,50,51),36,4,TimeSheet))))
That works Maabadi, Thanks so much! You are the best!
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,008
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're Welcome & Thanks for feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,776
Messages
5,574,181
Members
412,575
Latest member
Zakaria Mahama Saani
Top