MAXIFS - transfers NUMBERS but wont transfer <1.6 from one tab to another

[Wicca]

The problem is a "simple one" I am sure.
I am using the MAXIFS formula but it is not transferring all the data.
I have tried to put an explanation in the attached spreadsheet.
While there are about 30 tabs to the spreadsheet I have left two open. The rest I dont think are relevant to the case.

Have a fantastic day and thank you in advance for your help.

Kind regards


Invest in yourself, you are worth more than you think.


With respect
Wicca

Believe in yourself, you are worth the investment.

 

[Andrzejek]

The number 3.3 will trasfer over fine, but the <1.6 wont.

Isn't that because [tt]3.3[/tt] is a Number, but [tt]<1.6[/tt] is not

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Wicca]

Thanks Andy,
I finally figured that part of it out,
I stripped the < out and I realised it works fine, however I do need the < sign as its for legal compliance
Can anyone help me with a way to get them across to the right tab?

I have also tried index/match but the same result.

I was thinking of a macro, but the system wipes macro's for security reasons, so that idea is dead in the water . . . hmmmmmm

Have a fantastic weekend and keep warm and safe.

With respect
Wicca

Believe in yourself, you are worth the investment.

 

[Andrzejek]

I was thinking of a macro

You could create a Macro to do your magic.
If your Macro will drop the values into cells, you still can save your file as [tt]xlsx[/tt]
If that would be a UDF (User Defined Function), you can copy the cells and paste them back as values only. The same process. You do not have to provide your Macro to other people.



---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Wicca]

The file is transmitted from the server at 4am to the users.
Data is collated from various tabs to this tab for the users to use.
As I noted above, macros are stripped from the file before it is generated so macros are not available.
Obviously harder than I anticipated.
Never mind, all is good and it will have to be done manually.

Have a great day.



With respect
Wicca

Believe in yourself, you are worth the investment.

 

[SkipVought]

You can have macros in a Private.xlsb file that is open and hidden whenever you have a file open in Excel.

https://support.microsoft.com/en-us...workbook-aa439b90-f836-4381-97f0-6e4c3f5ee566

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Wicca]

This spreadsheet is emailed to several different users at about 4am daily (weekends included).
It is also opened on random occasions by other users wanting information from it.

To use a macro in this way would be extremely difficult to administer.

Please remember, I am a user that has been given the task of sorting this out . . . I have very little technical/programming knowledge.

BUT thanks anyway guys, I will try to work it with a combination of formulae in the cells concerned and see what I can come up with.

Stripping the > is the easy part, putting it back with all the other data is the hard bit . . . . have a great day and may your god bless and keep you safe.

With respect
Wicca

Believe in yourself, you are worth the investment.

 

[Andrzejek]

To use a macro in this way would be extremely difficult to administer.

No, it would not be.

Since you are "a user that has been given the task of sorting this out", create a Macro to do the "sorting this out", keep your Macro in Personal.xlsb (you do NOT need to share your Macro with anybody).

Whatever you do right now 'by-hand' in Excel, you can do it with your Macro:[ul][li]grab the Excel file from the server[/li]
[li]do your magic by using your Macro stored in your Personal.xlsb[/li]
[li]save the Excel file back to the server as [tt]*.xlsx[/tt] (no macro included!)[/li]
[/ul]


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

BTW, you can RECORD a macro and then the dialog will ask you where to store it, including your Private.xlsb workbook. You will be able to "see" this workbook when you open the VB Editor (Alt+F11)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

Skip,

Wicca may get confused between (what you call) Private.xlsb and (actual) Personal.xlsb

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

sorry you are correct

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Wicca]

Sorry guys,
Now I am even more confused. I will try to explain the process from go to whoa

1. Field rep gets data and enters it into an app
2. App enters it into a 3rd party server
3. 3rd party server sends daily report out at about 4am
4. Report emailed directly to me, field reps (different server), service provider (different server again), Council Staff (different server again)
5. Sometimes the data is requested by and emailed to an individual for compliance/statistical use (they could be on a government server).
6. Sometimes back issues of the report are requested by any one of the above recipients.

So I need the < symbol in the report for each recipient regardless of where they are or when they get it.

Does this make any sense?

Thanks

With respect
Wicca

Believe in yourself, you are worth the investment.

 

[SkipVought]

Wicca,

You have a date anomaly on Sheet Data Tab - Whanga SBR on Rows 185 & 186 for lookup date Sep 23, 2023 that displays a TIME value of 3:00 PM.

This solution trades on the observation that there are 2 and only 2 lookup entries for each date on that sheet.

So this formula does this...
If there's an error, return 0
If the Date/Lab - SBR Effluent Tests (INDEX & MATCH lookup) returns a 0, then get the SBR Effluent Test in the next Row.


Q20: =IFERROR(IF(INDEX('Data Tab - Whanga SBR'!$AJ$37:$AJ$186,MATCH(A20,'Data Tab - Whanga SBR'!$V$37:$V$186,0))=0,INDEX('Data Tab - Whanga SBR'!$AJ$37:$AJ$186,MATCH(A20,'Data Tab - Whanga SBR'!$V$37:$V$186,0)+1),INDEX('Data Tab - Whanga SBR'!$AJ$37:$AJ$186,MATCH(A20,'Data Tab - Whanga SBR'!$V$37:$V$186,0))),0)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

Oh, yes, your workbook...

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

So, where in this:
1...
2...
3...
4...
5...
6...
you have the ability/responsibility to do your magic with "sorting this out"?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

@Andy, I think the OP was laying out the scope of the issues he's trying to deal with and at the end, "I need the < symbol in the report for each recipient regardless of where they are or when they get it."

I've had similar circumstances, gathering information at sundry times and in divers manners and getting it all lined up and consistent for periodic analysis/reporting. I can imagine that once the "< symbol" problem is solved, that he's got a lot more challenges.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Wicca]

Ahhhh Skip, you have such great foresight . . .
I have finally got it sorted, rather complicated (in my mind anyway)
I moved the data to another tab and came up with this, it seems to work ok (Fingers Crossed)

=IFERROR(IF(ISBLANK(INDEX('Whangamata Data'!$BN$5:$BN$900,MATCH($A8,'Whangamata Data'!$BM$5:$BM$900,0))),"",INDEX('Whangamata Data'!$BN$5:$BN$900,MATCH($A8,'Whangamata Data'!$BM$5:$BM$900,0))),"")

This is where it all happened . . . hope it helps someone at some time

Have a fantastic day and know I think you guys are awesome.

Keep safe.

With respect
Wicca

Believe in yourself, you are worth the investment.