Nested Indirect Formula

[martinpgibson]

Hi there,

I could go into a lot of detail of what's going on in my Excel doc but to keep it simple I have this formula here that works great:

=SUM(INDIRECT("'"&TEXT(B$2,"DDMMYY")&"'!B17:BZ17"))

at the end of the above formula it has the number 17 twice. Where it says 17 what I want to do is place the following formula:

MATCH(A26,INDIRECT("'"&TEXT(B$2,"DDMMYY")&"'!$A$1:$A$100"))

That above formula returns the value 17. I have tried all sorts of combinations using & symbols and different quotation marks but I just can't figure it out. I would really appreciate your help, thank you!

[FDibbins]

without seeing any of your data, its hard to say, but have you tried putting that entire 2nd formula in an indirect() statement?

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[martinpgibson]

Hi FDibbins, I considered attaching a worksheet and I normally do on this forum, however in this particular instance it would be more helpful to me and others who may come across this post in the future to not get into the details of the rest of the formula. This issue is a syntax issue of how to put a formula inside an indirect formula, the other details of the formula don't add or take away from the problem at hand. And yes I have tried just copying and pasting the formula inside the indirect formula. Thanks

[vlady]

Are you saying changing the numbers 17 on the return values of the match?
ie. if the match return 10

=SUM(INDIRECT("'"&TEXT(B$2,"DDMMYY")&"'!B10:BZ10"))

[benishiryo]

hi martinpgibson. like FDibbins, i think uploading a sample Excel file helps much more. otherwise, we would have trouble testing. try this though:

Please Login or Register  to view this content.

[dredwolf]

The first formula uses relative addressing, your second fomula is using absolute addressing ($) and is addressing a single row, not sure if that's the problem, but is definitely something I would look at..
Also, the Indirect seems to be Trying to combine 2 different column references..

A sample workbook would certainly help resolve this

[martinpgibson]

Thanks so much benishiryo, you are right on the money! Thanks for understanding what I meant, I knew I was close but you saved me hours of grief.