So, I've been tasked with creating an automated calculation for my work. I'm not a novice at excel, but I am with macros.
Ideally, I'd like for cells to be hidden based on the three possible outcomes determined in cell D39 [=IF(B36>E9,"MOVE TO PWD DISBURSEMENT TRACKING SHEET",IF(E9=B36,"STOP! NO FURTHER ACTION NECESSARY",IF(B36<E9,"MOVE TO STEP 4. Aid to be Disbursed or Returned")))]
If "MOVE TO PWD DISBURSEMENT TRACKING SHEET" - Rows 42-82 should be hidden
If "STOP! NO FURTHER ACTION NECESSARY" - Rows 42-103 should be hidden
If "MOVE TO STEP 4. Aid to be Disbursed or Returned" - Rows 83-103 should be hidden
Any help you can provide would be GREATLY appreciated!!
Jason
[Solved] Macro to hide cells based on outcome
[Solved] Macro to hide cells based on outcome
Last edited by MrProgrammer on Sun Nov 08, 2020 6:35 am, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Reason: Tagged ✓ [Solved]
Office 2010, Windows 7
Re: Macro to hide cells based on outcome
Hide cell contents in Calc Spreadsheets:
viewtopic.php?f=101&t=54749
If you're after help with Excel, this forum maybe:
http://www.vbforums.com/forumdisplay.ph ... evelopment
viewtopic.php?f=101&t=54749
If you're after help with Excel, this forum maybe:
http://www.vbforums.com/forumdisplay.ph ... evelopment
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
- MrProgrammer
- Moderator
- Posts: 4905
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Macro to hide cells based on outcome
Hi, and welcome to the forum.
But your explanation suggests that you want rows to be hidden, not cells. The standard way in Calc to hide cells is with a filter. Data → Filter → Advanced filter will do what you need without the need for evil macros. Open the attachment. Change either B36 or E9. The text in D39 will change accordingly. To hide the desired rows, click A41 and use Data → Refresh Range.
If you need the latter actions automated, use Tools → Macros → Record Macro to record clicking A41 and refreshing. Also see [Tutorial] Binding a macro: Shortcut key, menu or toolbar.
If you want the rows to hide/unhide automagically, without a range refresh see [Tutorial] Sorting and Filtering data with formulas.
Read about conditional formatting and/or recording macros in Help → Index or in User Guides (PDF) or searching for topics about them in the Calc Forum.
[Tutorial] Ten concepts that every Calc user should know
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
If you want cell contents conditionally hidden, use Format → Conditional Formatting and a cell style which specifies number format ;;; (three semicolons).jcc1981 wrote:I'd like for cells to be hidden based on the three possible outcomes determined in cell D39
But your explanation suggests that you want rows to be hidden, not cells. The standard way in Calc to hide cells is with a filter. Data → Filter → Advanced filter will do what you need without the need for evil macros. Open the attachment. Change either B36 or E9. The text in D39 will change accordingly. To hide the desired rows, click A41 and use Data → Refresh Range.
If you need the latter actions automated, use Tools → Macros → Record Macro to record clicking A41 and refreshing. Also see [Tutorial] Binding a macro: Shortcut key, menu or toolbar.
If you want the rows to hide/unhide automagically, without a range refresh see [Tutorial] Sorting and Filtering data with formulas.
Read about conditional formatting and/or recording macros in Help → Index or in User Guides (PDF) or searching for topics about them in the Calc Forum.
[Tutorial] Ten concepts that every Calc user should know
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
- Attachments
-
- 201703071420.ods
- (8.57 KiB) Downloaded 167 times
Last edited by MrProgrammer on Wed Mar 08, 2017 7:46 pm, edited 1 time in total.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).