MS Excel Formula; Hide #REF!
-
Looking for a bit of help on this one, as Google results keep coming back on how to hide N/A cells.
This workbook is what I have to use to keep track of my daily/weekly hours when working remote. This in addition to the other three places I enter time into. I am required to keep track of time in fifteen minute increments.
Sheet works fine, and I just copy the previous days sheet for the formatting. I added an index page, and a link back to the index page on each sheet for fast navigation, this workbook covers the last five months, one sheet per day remote.
But, the index sheet I have built out has rows for 125 days/weeks, and is three columns. Col 1 is the incremental number, Col2 is the tab name, and Col3 is the Hyperlink to the tab.
Right now, I have forty lines that is tab number, #REF! (Tab name), #REF! (Hyperlink).
Using conditional formatting with the =iferror( isn’t blanking out the cells as I would like it,. And really, would rather see the whole row being blank if the tab doesn’t exist yet.
I know that a simple way is to purge the extra rows and just add them when I add another sheet. But, just trying to save myself some extra work if I can, even though it’s just a copy / paste.
Any thoughts?
-
-
@stacksofplates said in MS Excel Formula; Hide #REF!:
Does this work? https://support.microsoft.com/en-us/office/hide-error-values-and-error-indicators-in-cells-d171b96e-8fb4-4863-a1ba-b64557474439
Actually - yes.
It first didn’t make sense, since it mentions just A1, and implies will do while column. But when I highlight the whole column- it blanks it.
Time to up date and back copy,...
It’s just 50 tabs.
-
@gjacobse said in MS Excel Formula; Hide #REF!:
@stacksofplates said in MS Excel Formula; Hide #REF!:
Does this work? https://support.microsoft.com/en-us/office/hide-error-values-and-error-indicators-in-cells-d171b96e-8fb4-4863-a1ba-b64557474439
Actually - yes.
It first didn’t make sense, since it mentions just A1, and implies will do while column. But when I highlight the whole column- it blanks it.
Time to up date and back copy,...
It’s just 50 tabs.
That's your punishment for using excel for something it wasn't meant to do. :face_with_raised_eyebrow:
MS Access would have been a better tool, if you are hell bent on using Microsoft Office products.
-
@gjacobse said in MS Excel Formula; Hide #REF!:
@stacksofplates said in MS Excel Formula; Hide #REF!:
Does this work? https://support.microsoft.com/en-us/office/hide-error-values-and-error-indicators-in-cells-d171b96e-8fb4-4863-a1ba-b64557474439
Actually - yes.
It first didn’t make sense, since it mentions just A1, and implies will do while column. But when I highlight the whole column- it blanks it.
Time to up date and back copy,...
It’s just 50 tabs.
Please tell me this spreadsheet did not originate from MS Dynamics?
-
@Pete-S said in MS Excel Formula; Hide #REF!:
@gjacobse said in MS Excel Formula; Hide #REF!:
@stacksofplates said in MS Excel Formula; Hide #REF!:
Does this work? https://support.microsoft.com/en-us/office/hide-error-values-and-error-indicators-in-cells-d171b96e-8fb4-4863-a1ba-b64557474439
Actually - yes.
It first didn’t make sense, since it mentions just A1, and implies will do while column. But when I highlight the whole column- it blanks it.
Time to up date and back copy,...
It’s just 50 tabs.
That's your punishment for using excel for something it wasn't meant to do. :face_with_raised_eyebrow:
MS Access would have been a better tool, if you are hell bent on using Microsoft Office products.
This workbook is what I have to use to keep track of my daily/weekly hours when working remote.
-
I know you have to use this Gene. But if you are able to use something else at some point Notion is an awesome tool. I use it for almost all of my notes and a ton of different other things. You could easily do a time tracking app in it if it helps.
-
@Pete-S said in MS Excel Formula; Hide #REF!:
@gjacobse said in MS Excel Formula; Hide #REF!:
@stacksofplates said in MS Excel Formula; Hide #REF!:
Does this work? https://support.microsoft.com/en-us/office/hide-error-values-and-error-indicators-in-cells-d171b96e-8fb4-4863-a1ba-b64557474439
Actually - yes.
It first didn’t make sense, since it mentions just A1, and implies will do while column. But when I highlight the whole column- it blanks it.
Time to up date and back copy,...
It’s just 50 tabs.
That's your punishment for using excel for something it wasn't meant to do. :face_with_raised_eyebrow:
MS Access would have been a better tool, if you are hell bent on using Microsoft Office products.
Omg
Uh no Access not even
-
@stacksofplates said in MS Excel Formula; Hide #REF!:
I know you have to use this Gene. But if you are able to use something else at some point Notion is an awesome tool. I use it for almost all of my notes and a ton of different other things. You could easily do a time tracking app in it if it helps.
I’ll look- and hold comments till then.
-
@stacksofplates said in MS Excel Formula; Hide #REF!:
Notion
yea - I may come back and use it some time in the future,.. but for this use... I'm not. and then there is this:
I really don't need yet another thing, I may not use beyond this contract. - that said.. I likely could, but I'm not exposing anymore of my personal contact information with them I don't have to..
-
@gjacobse said in MS Excel Formula; Hide #REF!:
@stacksofplates said in MS Excel Formula; Hide #REF!:
Notion
yea - I may come back and use it some time in the future,.. but for this use... I'm not. and then there is this:
I really don't need yet another thing, I may not use beyond this contract. - that said.. I likely could, but I'm not exposing anymore of my personal contact information with them I don't have to..
Clockify is great. I use that all of the time.
-
@gjacobse said in MS Excel Formula; Hide #REF!:
@stacksofplates said in MS Excel Formula; Hide #REF!:
I know you have to use this Gene. But if you are able to use something else at some point Notion is an awesome tool. I use it for almost all of my notes and a ton of different other things. You could easily do a time tracking app in it if it helps.
I’ll look- and hold comments till then.
Here's a quick one I put together. You could do manual time or calculated from the date. And the cool thing is everything can be related. So the second screenshot shows what happens if you click on "Did Thing" which is it's own page (like an object).
-
@gjacobse said in MS Excel Formula; Hide #REF!:
@stacksofplates said in MS Excel Formula; Hide #REF!:
I know you have to use this Gene. But if you are able to use something else at some point Notion is an awesome tool. I use it for almost all of my notes and a ton of different other things. You could easily do a time tracking app in it if it helps.
I’ll look- and hold comments till then.
Yeah Notion is good for a lot of things.
-
@gjacobse said in MS Excel Formula; Hide #REF!:
@stacksofplates said in MS Excel Formula; Hide #REF!:
Notion
yea - I may come back and use it some time in the future,.. but for this use... I'm not. and then there is this:
I really don't need yet another thing, I may not use beyond this contract. - that said.. I likely could, but I'm not exposing anymore of my personal contact information with them I don't have to..
Another option is a Kanban board. You can track time through moving your cards across and then get reports on time cards have spent in each column. Kanboard works well and is open source and has nice reports. If you don't want to run it yourself things like Trello work decently as well but I can't speak to the reports you can get from it.
-
@stacksofplates said in MS Excel Formula; Hide #REF!:
Another option is a Kanban board
Is Decks in Nextcloud a type of KanBan?
-
@JasGot said in MS Excel Formula; Hide #REF!:
@stacksofplates said in MS Excel Formula; Hide #REF!:
Another option is a Kanban board
Is Decks in Nextcloud a type of KanBan?
Yes
-
@stacksofplates said in MS Excel Formula; Hide #REF!:
@gjacobse said in MS Excel Formula; Hide #REF!:
@stacksofplates said in MS Excel Formula; Hide #REF!:
Notion
yea - I may come back and use it some time in the future,.. but for this use... I'm not. and then there is this:
I really don't need yet another thing, I may not use beyond this contract. - that said.. I likely could, but I'm not exposing anymore of my personal contact information with them I don't have to..
Another option is a Kanban board. You can track time through moving your cards across and then get reports on time cards have spent in each column. Kanboard works well and is open source and has nice reports. If you don't want to run it yourself things like Trello work decently as well but I can't speak to the reports you can get from it.
I used Trello for someone’s tracking-
Hated it. Maybe I need to use it more,... maybe -
We use Trello and like it a lot, but we use it for a coding process, not job tracking.
