help using indirect()

[jml2]

I'm new to the indirect function. If I put a full address of a cell in another cell I have gotten it to work. But now I have part of the address (the row) in a cell and need to be able to combine that with what I know.

I have a table with names in column B and dates in row 1. The rest of the tables is the sales amount for the employee listed in column B on the date in row 1. (see data sheet). I want to find the max and the average for certain days. I can do this if the names are in the same order in the report and the data file. but this isn't always the case.

I can use a match to find the right row to look.
=MATCH(A2,data!B:B,0)
So I thought using this knowledge I could use indirect. exceljet.net had an article at formula/indirect-named-range-different-sheet

So I tried this: {=MAX(MAXIFS(INDIRECT("'data'!$c'"&B2&":$N"&B2),data!$C$1:$N$1,report!$I$1:$L$1))}
but I got #REF



The file is attached.

[jml2]

Solved my own problem. I had an extra ' in there that was messing things up.
Change to:
{=MAX(MAXIFS(INDIRECT("'data'!$c"&B2&":$N"&B2),data!$C$1:$N$1,report!$I$1:$L$1))}

And it worked perfectly. Sorry to waste everyone's time

[jomaor1]

you just got one little thingy wrong

MAX(MAXIFS(INDIRECT("'data'!$c'"&B2&":$N"&B2),data!$C$1:$N$1,report!$I$1:$L$1))

that ' shouldn't be there,

Cheers!

[Thomas Blume]

I use this formula to find a minimum value in a column of numbers: =MAXIFS(BG7:BG10000;BG7:BG10000;"<1.04") giving the correct answer 1.03822

So works fine - but I would like to be able to change the <1.04 criteria using an external cell.....

I have tried this: =MINIFS(BG7:BG10000;BG7:BG10000;INDIRECT("">"&A2&""")) but I just get a clean 0 as answer (A2 cell contains the 1.04 value).

Could you pls advice?

[AliGW]

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread