Question description:
This user has given permission to use the problem statement for this
blog.
I need a formula created that will create add totals from 2 dynamically created sheet in a third sheet. List of items in column A with remainders of columns created dynamically depending on what is entered in over sheets but will always be one of 82 variables
Solved by B. B. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
05/06/2018 - 08:26
Hi!
User
05/06/2018 - 08:26
hello!
Excelchat Expert
05/06/2018 - 08:27
Please describe me your problem
User
05/06/2018 - 08:28
https://docs.google.com/spreadsheets/d/1iIx8_UJrRZokHgVDgPBHAsHlpfv5AqoTdz6DMRnmvpQ/edit?usp=sharing
Excelchat Expert
05/06/2018 - 08:29
And want do you want from me? How I can help you?
User
05/06/2018 - 08:29
please download the file, so that I can tell you more about it
User
05/06/2018 - 08:29
I need a formula created that will create add totals from 2 dynamically created sheet in a third sheet. List of items in column A with remainders of columns created dynamically depending on what is entered in over sheets but will always be one of 82 variables
User
05/06/2018 - 08:30
look at sheet RawData_Totals
User
05/06/2018 - 08:30
the list of Divisions is in Data_CostCode
User
05/06/2018 - 08:31
if you look at sheet Data_CostCode, there are 82 Divisions
Excelchat Expert
05/06/2018 - 08:31
yes
User
05/06/2018 - 08:31
if you look at sheet "1" for example F11 - I11 you can select divisions that will show up in sheet RawData_NonClinical
User
05/06/2018 - 08:32
if the division is not selected in any of the sheets "1" -"30" it should not show up along the top of sheet RawData_Totals
User
05/06/2018 - 08:32
am I clear till now?
User
05/06/2018 - 08:34
there?
Excelchat Expert
05/06/2018 - 08:34
give me a minute to analyse
User
05/06/2018 - 08:35
sure
User
05/06/2018 - 08:37
## the formula is to be entered in the RawData_totals ##
User
05/06/2018 - 08:37
## all divisions selected from the sheets 1 to 30 should be on top of the RawData_Totals. If a division is not selected in any of the sheets, it should not show up on top of the RawData_Totals ##
Excelchat Expert
05/06/2018 - 08:38
ok, I understand
Excelchat Expert
05/06/2018 - 08:38
I need a few minutes to write the formula.
User
05/06/2018 - 08:39
ok just want to know whether you will use match and index or vlookup?
Excelchat Expert
05/06/2018 - 08:40
I will try witch vlookup
Excelchat Expert
05/06/2018 - 08:40
with*
User
05/06/2018 - 08:42
ok
Excelchat Expert
05/06/2018 - 08:42
I will need the extension
User
05/06/2018 - 08:43
sorry?
User
05/06/2018 - 08:43
ok done
User
05/06/2018 - 08:44
please be quick as I need few more things in the sheet
Excelchat Expert
05/06/2018 - 08:45
ok
Excelchat Expert
05/06/2018 - 08:51
tell me why your solution is bad?
User
05/06/2018 - 08:52
tell me why your solution is bad? sorry?
Excelchat Expert
05/06/2018 - 08:52
I have analyzed all sheets and in "Totals" only those that exist in 1-30 are displayed to you
Excelchat Expert
05/06/2018 - 08:53
when adding / removing a division, the helper is updated and in "Totals" divisions are added or removed at the top
User
05/06/2018 - 08:55
the formula I have added has 2 limitation and I want something that will help me over come that
User
05/06/2018 - 08:55
1. Unfortunately, the RawData_Totals also include selected divisions from hidden sheets. So if sheet 4 is hidden but is the only one to have selected Broken Hill Division, that division will still show up in the RawData_Totals. If you wish for this to not show up, this will need to be "de-selected" from sheet 4, or probably we could just remove the selected divisions before hiding a sheet.
User
05/06/2018 - 08:55
No. 2 limitation is that in RawData_Totals, D1:CF2 should not be deleted as these form part of the displaying of selected divisions across the different sheets.
Excelchat Expert
05/06/2018 - 08:57
1. if you want hidden tabs not to be considered, then you should use VBA
Excelchat Expert
05/06/2018 - 08:59
2. Block these cells and you will not be able to delete them.
User
05/06/2018 - 09:00
I think I have a possible way of fixing issue 1
User
05/06/2018 - 09:01
just not sure how best to do it
User
05/06/2018 - 09:01
could you add to formula of helper so that if sheet overview has sheet 4 as No they all =o
User
05/06/2018 - 09:01
for a sheet to be active it need to have yes in sheet overview for helper sheet B5 -E5
User
05/06/2018 - 09:03
can we do that?
Excelchat Expert
05/06/2018 - 09:03
I try to use vba to check visible
User
05/06/2018 - 09:03
ok
User
05/06/2018 - 09:04
can you please help me with one more VBA as I am not good in it
User
05/06/2018 - 09:05
could you also create a button on the Overview sheet that prints the Overview sheet as well as sheets 1 -30 if they are active (yes in column C on Overview sheer)? each sheet printed to PDF a single PDF file (one pdf file for all sheets printed). each sheet would fit a single A4 Page
User
05/06/2018 - 09:10
there?
Excelchat Expert
05/06/2018 - 09:14
ok, please create module in vba
Excelchat Expert
05/06/2018 - 09:14
and paste this code:
Excelchat Expert
05/06/2018 - 09:14
Option Explicit
Function IsSheetVisible(rng As Range) As Variant
Application.Volatile
IsSheetVisible = CBool(rng.Parent.Visible = xlSheetVisible)
End Function
User
05/06/2018 - 09:16
may I know what the use of it?
Excelchat Expert
05/06/2018 - 09:16
the function IsSheetVisible return tru or false based on whether the sheet is hidden or not
User
05/06/2018 - 09:17
ohk
User
05/06/2018 - 09:18
its not working
User
05/06/2018 - 09:18
time is running out. How I should find you again to solve this issue
User
05/06/2018 - 09:19
I don't want to delay with any other expert now. its really time consuming and frustrating to repeat same thing again n again'
Excelchat Expert
05/06/2018 - 09:21
Unfortunately, everything must be done through the Got It platform. You must re-set a task and find me
User
05/06/2018 - 09:21
You must re-set a task and find me, yes how should I do it?
User
05/06/2018 - 09:24
I will re-post the project with the top heading Vlookup (ID: Anonymous Shrew)? will you able to pick up
Excelchat Expert
05/06/2018 - 09:24
No,
Excelchat Expert
05/06/2018 - 09:24
Can you enable editing the google sheet file?
User
05/06/2018 - 09:24
no
Excelchat Expert
05/06/2018 - 09:25
why?
User
05/06/2018 - 09:25
option is not there
User
05/06/2018 - 09:25
where I will get the option
User
05/06/2018 - 09:25
which sheet that I have shared ?
Excelchat Expert
05/06/2018 - 09:25
in sharing
User
05/06/2018 - 09:26
oh yes I can do that
Excelchat Expert
05/06/2018 - 09:26
https://docs.google.com/spreadsheets/d/1iIx8_UJrRZokHgVDgPBHAsHlpfv5AqoTdz6DMRnmvpQ/edit#gid=2083687757
User
05/06/2018 - 09:26
wait
User
05/06/2018 - 09:26
please check
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
