# Excel - COLUMN Function Problem - Expert Solution

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
User 05/06/2018 - 08:28
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
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
User 05/06/2018 - 08:52
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
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
User 05/06/2018 - 09:26
wait
User 05/06/2018 - 09:26

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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.