×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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.

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

Quote (Wicca)

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

Isn't that because 3.3 is a Number, but <1.6 is not ponder

---- Andy

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

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

(OP)
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 . . . hmmmmmmsadeyes

Have a fantastic weekend and keep warm and safe.

With respect
Wicca

Believe in yourself, you are worth the investment.

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

Quote (Wicca)

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 xlsx
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.
wiggle

---- Andy

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

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

(OP)
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.

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

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/office/copy-yo...

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

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

(OP)
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.

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

Quote (Wicca)

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

No, it would not be. smile

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:
  • grab the Excel file from the server
  • do your magic by using your Macro stored in your Personal.xlsb
  • save the Excel file back to the server as *.xlsx (no macro included!)
wiggle

---- Andy

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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

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

Skip,

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


wavey

---- Andy

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

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

blushsorry you are correctblush

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

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

(OP)
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.

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

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

Oh, yes, your workbook...

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

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

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

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

@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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

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

(OP)
Ahhhh Skip, you have such great foresight . . . dazed
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close