Problem copying down an INDIRECT formula

T

thewiseguy

Well-known Member
Joined
May 23, 2005
Messages
954
Office Version
  1. 365
Platform
  1. Windows
When I try to copy this formula down, it stays the same and won't reference the cells I need it to....any ideas??


=INDIRECT("PO_Order_Form!B5")
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Do you need INDIRECT, why not just

=PO_Order_Form!B5
Click to expand...


the cells being referenced may be deleted, so the INDIRECT allows me to keep the values I need.
 
Upvote 0
=INDIRECT("PO_Order_Form!B"&5)

This will increment the "5" as you copy the formula down.
 
Upvote 0
=INDIRECT("PO_Order_Form!B"&5)

This will increment the "5" as you copy the formula down.
Click to expand...

not working. it's still copying the same formula. ??
 
Upvote 0
If the first instance of the formula is in A1 then try this copied down

=INDIRECT("PO_Order_Form!B"&ROW()-ROW(A$1)+5)
 
Upvote 0
If the first instance of the formula is in A1 then try this copied down

=INDIRECT("PO_Order_Form!B"&ROW()-ROW(A$1)+5)
Click to expand...

still copying the same formula. I am in 2007....not sure if that is making the difference here??
 
Upvote 0
If the first instance of the formula is in A1 then try this copied down

=INDIRECT("PO_Order_Form!B"&ROW()-ROW(A$1)+5)
Click to expand...


OK it worked. I was confused about the formula and which parts referenced my current sheet vs my reference sheet.

Thanks Barry!
 
Upvote 0
Well the formula itself will look identical in each cell but it should evaluate to a different result because ROW() will evaluate to 1 in row 1, to 2 in row 2 etc.

If you have different value in PO_Order_Form!B5 and PO_Order_Form!B6 you should notice the difference.

If you have calculation set to manual also try pressing F9 to re-calculate.....
 
Upvote 0
Now my problem is when I try to sort the data in my table, the reference function won't allow it. Is there anyway around this or do I need to copy-paste-values in order to do this?
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Indirect Formula help
Replies
7
Views
148
ItalianPlatinum
I
B
  • Question
vba formula problem
Replies
4
Views
173
BobtBuilder
B
N
  • Solved
Use different sheet names as part of a formula (indirect function?)
Replies
3
Views
309
awoohaw
awoohaw
S
  • Solved
Dynamic Sheet using Drop Down List & INDIRECT using External File
Replies
5
Views
368
smozgur
smozgur
S
  • Solved
Indirect Formula
Replies
3
Views
175
Fluff
Fluff

Forum statistics

Threads
1,215,007
Messages
6,122,670
Members
449,091
Latest member
peppernaut

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top