Indirect Formula not working

wakisme

New Member
Joined
Jul 21, 2014
Messages
19
I have a spreadsheet with 2 worksheets. One named Test Data and the other named TFR
The following formula works perfectly in the Test Data worksheet but has a #value error when the same formula is used in the TFR worksheet

Excel Formula:
=CELL("address";(OFFSET(INDEX('Test Data'!$A$8:(INDIRECT('Test Data'!W4));MATCH(TFR!D3;'Test Data'!$A$8:(INDIRECT('Test Data'!W4)))+1;);0;3)))


When I run Evaluate Formulas in the TFR worksheet the #value error occurs at this point
Excel Formula:
('Test Data'!$A$8:(INDIRECT('Test Data'!W4))

I have spent hours trying to work this out so if someone could help, I would be very grateful.
Thanks
 
Last edited by a moderator:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Best to provide screenshot or xl2bb sample if you need help.
 
Upvote 0
Try
=CELL("address";(OFFSET(INDEX('Test Data'!$A$8:(INDIRECT("'Test Data'!W4"));MATCH(TFR!D3;'Test Data'!$A$8:(INDIRECT("'Test Data'!W4")))+1;);0;3)))
 
Upvote 0
Try
=CELL("address";(OFFSET(INDEX('Test Data'!$A$8:(INDIRECT("'Test Data'!W4"));MATCH(TFR!D3;'Test Data'!$A$8:(INDIRECT("'Test Data'!W4")))+1;);0;3)))
Thanks for your reply. Changing the formula in the way you suggested changes the Range to A8:W8 when I need A8:A1114 (in this instance)
 
Upvote 0
Best to provide screenshot or xl2bb sample if you need help.
Thanks for your reply. I will need to work out what an xl2bb sample is tomorrow. A screen shot would not be showing much as there is just a #value error and the above formula
 
Upvote 0
I have a spreadsheet with 2 worksheets. One named Test Data and the other named TFR
The following formula works perfectly in the Test Data worksheet but has a #value error when the same formula is used in the TFR worksheet

Excel Formula:
=CELL("address";(OFFSET(INDEX('Test Data'!$A$8:(INDIRECT('Test Data'!W4));MATCH(TFR!D3;'Test Data'!$A$8:(INDIRECT('Test Data'!W4)))+1;);0;3)))


When I run Evaluate Formulas in the TFR worksheet the #value error occurs at this point
Excel Formula:
('Test Data'!$A$8:(INDIRECT('Test Data'!W4))

I have spent hours trying to work this out so if someone could help, I would be very grateful.
Thanks
what cells in each worksheet are you entering the formula in? if you are inserting the formula into cell that has different column/row address you may be creating #REF errors. I'm not sure if that would filter into a #VALUE error or not.

The other thing is to check your data types. You may have an extra space in text, or have a date or number that has a space in it or is formatted as text.

and as to posting xl2bb .... as I allude to in my 2nd paragraph... it may not be your formula that is wrong but your data. So, yes... an xl2bb mini worksheet would be very helpful. the forum cannot easily guess what your data looks like and provide a reasonable solution.

Finally, a real bonus would be to actually provide an expected result, so the forum as they are working through your question can get to your expected result.
 
Upvote 0
What is the value of
'Test Data'!W4
Thanks for your question. 'Test Data'!W4 has a formula in it which returns the cell address of the last used row on the worksheet Test Data - $A$1114
I have actually now used a different formula to use that gives me the result I needed but thanks for your help
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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