BlosMusic
asked on
Excel - Making one continuous column from three smaller ones
I have a column of numbers which refer to letters in the adjacent column. There are three similar column pairs, of varying length. How do I make one continuous column pair from all the data? From time to time the lengths of the columns will vary, depending on what is being put into the columns, of course. And I don't want any gaps in the resulting column-pair!
See attached 'sample'.
In fact the columns are on different worksheets, but I don't suppose that matters. All I want to do is put all the data into one continuous column-pair (i.e. the data from the three different column pairs will all string on one below the other). I want the results to go in the yellow shaded boxes. (See attached).
Thanks.
Sample-151130.xlsx
See attached 'sample'.
In fact the columns are on different worksheets, but I don't suppose that matters. All I want to do is put all the data into one continuous column-pair (i.e. the data from the three different column pairs will all string on one below the other). I want the results to go in the yellow shaded boxes. (See attached).
Thanks.
Sample-151130.xlsx
your question is not clear. can you put the example of few lines in the yellow cells. so that i know how the result of data should look like?
Hi,
pls try
pls try
Sub macro()
For Each sh In Array("Sheet1", "Sheet2")
If Sheets("Results").Range("M3") = "" Then
Set rngDest = Sheets("Results").Range("M3")
Else
Set rngDest = Sheets("Results").Range("M3").End(xlDown).Offset(1)
End If
Sheets(sh).Range("C3:D" & Range("C3").End(xlDown).Row).Copy Destination:=rngDest
Next
End Sub
Hi,
Example attached, please click on macro button to see the desired output.
Best,
Sample-151130.xlsb
Example attached, please click on macro button to see the desired output.
Best,
Sample-151130.xlsb
ASKER
Since you say they are on different sheets but are they on the same columns in these sheets?
ASKER
Excel amusant,
A few of the numbers are missing in your end result. However, i really wanted a formula (if possible) rather then VBA. Is that possible?
Thanks.
A few of the numbers are missing in your end result. However, i really wanted a formula (if possible) rather then VBA. Is that possible?
Thanks.
ASKER
Rgonzo1971. Yes they will be in the same columns on the various worksheets. But is there a formula, rather than VBA?
ASKER
ProfessorJimJam,
Yes, that's right.
Is this a formula or VBA?
Yes, that's right.
Is this a formula or VBA?
I can do it with formula. is that okay?
ASKER
Yes, very much so!
Thanks.
Thanks.
ASKER
I would really want to go down the formula route, not VBA.
Many thanks.
Many thanks.
@BlosMusic
please find attached file with formula solution.
i used Excel tables references because of its dynamic range benefit.
Sample-151130.xlsx
please find attached file with formula solution.
i used Excel tables references because of its dynamic range benefit.
Sample-151130.xlsx
ASKER
Wow - that certainly works! It's a heck of a formula!
My "real-life" spreadsheet, though, has 24 tables of this sort, each with FOUR columns, and each on a separate worksheet!
Is there a way that I can understand, that will enable your formula to be extended to cope with that?
Thanks.
My "real-life" spreadsheet, though, has 24 tables of this sort, each with FOUR columns, and each on a separate worksheet!
Is there a way that I can understand, that will enable your formula to be extended to cope with that?
Thanks.
ASKER
Maybe it can only be done with VBA.
But to restate my problem:
I have a large number of four-column tables (24 currently, but this will increase over time), one table per worksheet, and I want to put all the data from these tables, automatically, into a continuous four-column table on another, new, worksheet. The data in the tables on the 24+ worksheets gets into those tables from other sources, by means of various formulas. The bringing together of all this data from the 24+ tables needs to be automatic so that if, for example, another row of data appears in any one of the 24 tables, it will be inserted into the continuous table on the new worksheet.
Any ideas, anyone?
But to restate my problem:
I have a large number of four-column tables (24 currently, but this will increase over time), one table per worksheet, and I want to put all the data from these tables, automatically, into a continuous four-column table on another, new, worksheet. The data in the tables on the 24+ worksheets gets into those tables from other sources, by means of various formulas. The bringing together of all this data from the 24+ tables needs to be automatic so that if, for example, another row of data appears in any one of the 24 tables, it will be inserted into the continuous table on the new worksheet.
Any ideas, anyone?
You asked for formula solution and the solution I provided is formula based. Now that you have mentioned that for each sheet you have four columns it also can be done. What is important here is that you need to create tables for each of those sheets data, because tables are very effective in handling dynamic range , meaning that of your columns expands then the formula automatically will expand the union of columns as well.
ASKER
I have created those tables, each of which is called Table 1, table 2, etc., etc., but I don't know how to amend your formula to do the four columns and the 24+ tables. My problem is that I don't know about dynamic ranges. I could, and must, learn, but in the short term I need to get this combined table. I could send you the whole spreadsheet so you'll see what I am trying to do, but I do not really want the whole world to see it. And there's no reason why anyone should do my work for me! Maybe I'm over-complicating things . . . . .
it is so easy.
look into the attached modified formula. i added one more table making it total 4 tables and one union table
so, i created a new table. first i renamed my union table to table5 and then i created the new table as table4
then i added one additional IFERROR with adding the Index of (Table 4 but this time, see the pattern for previous ones, i added ROW([@Column1])-ROW(Table5
try to compare the previous file and this one. you will learn how to add new table.
please let me know, if you have any question.
Sample-151130.xlsx
look into the attached modified formula. i added one more table making it total 4 tables and one union table
so, i created a new table. first i renamed my union table to table5 and then i created the new table as table4
then i added one additional IFERROR with adding the Index of (Table 4 but this time, see the pattern for previous ones, i added ROW([@Column1])-ROW(Table5
try to compare the previous file and this one. you will learn how to add new table.
please let me know, if you have any question.
Sample-151130.xlsx
ASKER
I am trying to understand this. But as I have 24 or even 25 tables, doesn't this become nearly impossible? The formula will be an enormous length!
ASKER
In fact it gets more and more difficult. My actual spreadsheet has around 25 tables, each on a separate worksheet, each with four columns, and each with about 200 rows. How can I possibly make one continuous table from that?
Your formula is very good, but I guess what I want will be completely unwieldy if I do it this way!
Your formula is very good, but I guess what I want will be completely unwieldy if I do it this way!
It is possible to do it. All you need to break down and study my formula. It is easy if you look at it deeply. If you upload a dummy file similar to your original file, I can put the formula in that.
ASKER
I have tried studying your formula, and I have more or less got my head round it; but it's beginning to make my mind boggle when I start working with 23 tables!
I attach my entire spreadsheet.
Basically, all the numbered Tables (1-23), which are yellow on the various worksheets that have people's names on them (tabs 4 - 26), need to be added into a composite Table, without the headers, on the final tab (which I've called ".csv file").
If you do that for me, I will be eternally in your debt!
I attach the entire spreadsheet.
I know it's over-complicated, but it's grown itself organically!
Any question, let me know.
Many, many thanks.
I attach my entire spreadsheet.
Basically, all the numbered Tables (1-23), which are yellow on the various worksheets that have people's names on them (tabs 4 - 26), need to be added into a composite Table, without the headers, on the final tab (which I've called ".csv file").
If you do that for me, I will be eternally in your debt!
I attach the entire spreadsheet.
I know it's over-complicated, but it's grown itself organically!
Any question, let me know.
Many, many thanks.
ASKER
I didn't see if my spreadsheet was sent, so here it is again.
Input-hours-from-timesheets--Sylvia.xlsm
Input-hours-from-timesheets--Sylvia.xlsm
BlosMusic
i saw your workbook. i tried to understand it. but i could not find any sheet called "csv file"
so, all the yellow tables need to be combined in one table without their headers but this one table where is it located and what is the sheet name or range location for example
i need to know the sheet name and the range where the combined table need to be put for example from column A to Column D.
i saw your workbook. i tried to understand it. but i could not find any sheet called "csv file"
so, all the yellow tables need to be combined in one table without their headers but this one table where is it located and what is the sheet name or range location for example
i need to know the sheet name and the range where the combined table need to be put for example from column A to Column D.
ASKER
Sorry, Professor, I didn't name that worksheet. It is the last worksheet - just a new worksheet with currently nothing on it.
If you can combine all the yellow tables onto this new spreadsheet in Column A to D, that would be perfect.
Genius.
Thanks.
If you can combine all the yellow tables onto this new spreadsheet in Column A to D, that would be perfect.
Genius.
Thanks.
@BlosMusic
please see attached.
i created a worksheet named it "MAIN" and then all of the tables are combined with formula on this table. it is dynamic if your data tables on other sheets grow it will automatically populate in this Main sheet table. please note that the empty gaps you have in the main table is because some of your tables were empty and the first row after header had no data.
the formula was long and somehow complicated ;-) but i figured it out. here is the formula
please see attached.
i created a worksheet named it "MAIN" and then all of the tables are combined with formula on this table. it is dynamic if your data tables on other sheets grow it will automatically populate in this Main sheet table. please note that the empty gaps you have in the main table is because some of your tables were empty and the first row after header had no data.
the formula was long and somehow complicated ;-) but i figured it out. here is the formula
=IFERROR(IFERROR(INDEX(Table1, ROW([@Employee])-ROW(Table24[#Headers]),COLUMN(A:A)),IFERROR(INDEX(Table2, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1),COLUMN(A:A)),IFERROR(INDEX(Table3, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2),COLUMN(A:A)),IFERROR(INDEX(Table4, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3),COLUMN(A:A)),IFERROR(INDEX(Table5, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4),COLUMN(A:A)),IFERROR(INDEX(Table6, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5),COLUMN(A:A)),IFERROR(INDEX(Table7, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6),COLUMN(A:A)),IFERROR(INDEX(Table8, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table7),COLUMN(A:A)),IFERROR(INDEX(Table9, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8),COLUMN(A:A)),IFERROR(INDEX(Table10, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9),COLUMN(A:A)),IFERROR(INDEX(Table11, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10),COLUMN(A:A)),IFERROR(INDEX(Table12, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11),COLUMN(A:A)),IFERROR(INDEX(Table13, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12),COLUMN(A:A)),IFERROR(INDEX(Table14, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12)-ROWS(Table13),COLUMN(A:A)),IFERROR(INDEX(Table15, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12)-ROWS(Table13)-ROWS(Table14),COLUMN(A:A)),IFERROR(INDEX(Table16, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12)-ROWS(Table13)-ROWS(Table14)-ROWS(Table15),COLUMN(A:A)),IFERROR(INDEX(Table17, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12)-ROWS(Table13)-ROWS(Table14)-ROWS(Table15)-ROWS(Table16),COLUMN(A:A)),IFERROR(INDEX(Table18, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12)-ROWS(Table13)-ROWS(Table14)-ROWS(Table15)-ROWS(Table16)-ROWS(Table17),COLUMN(A:A)),IFERROR(INDEX(Table19, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12)-ROWS(Table13)-ROWS(Table14)-ROWS(Table15)-ROWS(Table16)-ROWS(Table17)-ROWS(Table18),COLUMN(A:A)),IFERROR(INDEX(Table20, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12)-ROWS(Table13)-ROWS(Table14)-ROWS(Table15)-ROWS(Table16)-ROWS(Table17)-ROWS(Table18)-ROWS(Table19),COLUMN(A:A)),IFERROR(INDEX(Table21, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12)-ROWS(Table13)-ROWS(Table14)-ROWS(Table15)-ROWS(Table16)-ROWS(Table17)-ROWS(Table18)-ROWS(Table19)-ROWS(Table20),COLUMN(A:A)),IFERROR(INDEX(Table22, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12)-ROWS(Table13)-ROWS(Table14)-ROWS(Table15)-ROWS(Table16)-ROWS(Table17)-ROWS(Table18)-ROWS(Table19)-ROWS(Table20)-ROWS(Table21),COLUMN(A:A)),INDEX(Table23, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12)-ROWS(Table13)-ROWS(Table14)-ROWS(Table15)-ROWS(Table16)-ROWS(Table17)-ROWS(Table18)-ROWS(Table19)-ROWS(Table20)-ROWS(Table21)-ROWS(Table22),COLUMN(A:A)))))))))))))))))))))))),"")ASKER
Seems brilliant. I will check it through and get back to you - but thanks very much indeed!!
you are welcome. thanks for the feedback.
ASKER
Hi,
I have tried it, but there is a problem. The yellow tables are now much shorter, and when (for example) I add more entries in the 'Timesheet Entries' worksheet, for (for example) A Jerrum, if you look on the 'A Jerrum' worksheet, there are only rows in the yellow table, and if I keep adding more entries (beyond 2) for A Jerrum, they don't appear in the MAIN worksheet table. I need the yellow tables to be as long as they originally were (about 200 rows of the 4-column yellow table). The same for each of the 24 yellow tables!
Any thoughts?
Thanks.
I have tried it, but there is a problem. The yellow tables are now much shorter, and when (for example) I add more entries in the 'Timesheet Entries' worksheet, for (for example) A Jerrum, if you look on the 'A Jerrum' worksheet, there are only rows in the yellow table, and if I keep adding more entries (beyond 2) for A Jerrum, they don't appear in the MAIN worksheet table. I need the yellow tables to be as long as they originally were (about 200 rows of the 4-column yellow table). The same for each of the 24 yellow tables!
Any thoughts?
Thanks.
i think you are making some sort of mistake with the data entry, becuase here when i enter data on the yellow tables it automatically gets popullated into the main worksheet table.
the problem is caused with your formulas in the yellow tables.
it has nothing to do with the MAIN sheet table formula.
for example , please open the attached file. i added more data in the Jerrum' worksheet and i put Professor in all cells and all of them appears in the Main Sheet table automatically.
you need to get your yellow sheets formulas fixed. becuase the MAIN sheet table formula is robust and i tested it and have no problem at all.
EE.xlsm
the problem is caused with your formulas in the yellow tables.
it has nothing to do with the MAIN sheet table formula.
for example , please open the attached file. i added more data in the Jerrum' worksheet and i put Professor in all cells and all of them appears in the Main Sheet table automatically.
you need to get your yellow sheets formulas fixed. becuase the MAIN sheet table formula is robust and i tested it and have no problem at all.
EE.xlsm
ASKER
Hi Prof,
I think I may have confused you, or not mentioned one thing: my entry point for data - my ONLY entry point - is on the 'Timesheet entries' worksheet. Everything goes on there, and it gets transmitted by my formulas to the yellow tables in the various worksheets with the peoples' names on (e.g. A Jerrum, A Short, etc.).
So all I want to do is get the data in the yellow tables, the data that has got to those tables automatically via the formulas, all starting from the 'Timesheet Entries' worksheet - I want to get the yellow table entries onto your MAIN worksheet. But I cannot and certainly must not, put anything straight into the yellow tables. In fact, in the end i want all my other worksheets except the 'Timesheet Entries' and 'MAIN' worksheets hidden from the data entry clerk so that she just enters her data onto the 'Timesheet Entries' worksheet, and then the resulting long table that your brilliant formula has produced can be exported to our accounts software.
Hope that makes sense. Also, i don't really want any gaps in the table rows on your MAIN worksheet.
Am I asking too much? Your help has been absolutely invaluable, and I will try to learn from your huge formula, but it's mind-boggling!
Thanks very much for your continuing help.
Input-hours-from-timesheets--Sylvia.xlsm
I think I may have confused you, or not mentioned one thing: my entry point for data - my ONLY entry point - is on the 'Timesheet entries' worksheet. Everything goes on there, and it gets transmitted by my formulas to the yellow tables in the various worksheets with the peoples' names on (e.g. A Jerrum, A Short, etc.).
So all I want to do is get the data in the yellow tables, the data that has got to those tables automatically via the formulas, all starting from the 'Timesheet Entries' worksheet - I want to get the yellow table entries onto your MAIN worksheet. But I cannot and certainly must not, put anything straight into the yellow tables. In fact, in the end i want all my other worksheets except the 'Timesheet Entries' and 'MAIN' worksheets hidden from the data entry clerk so that she just enters her data onto the 'Timesheet Entries' worksheet, and then the resulting long table that your brilliant formula has produced can be exported to our accounts software.
Hope that makes sense. Also, i don't really want any gaps in the table rows on your MAIN worksheet.
Am I asking too much? Your help has been absolutely invaluable, and I will try to learn from your huge formula, but it's mind-boggling!
Thanks very much for your continuing help.
Input-hours-from-timesheets--Sylvia.xlsm
@BlosMusic
Phew! it was a complicated work. this was the longest formula, i ever written.
well, i had to work again in your original file. now i understood that your yellow cell tables have to have their empty cells.
i reworked the formula with some other tricks and now it is working. i did not touch your other 23 yellow tables they are all intact. please find attached workbook. when any new data pops into the yellow tables. it will automatically pop into the Main Sheet Table24
plz let me know how it goes. hopefully this is the final wrap up
EE.xlsb
Phew! it was a complicated work. this was the longest formula, i ever written.
well, i had to work again in your original file. now i understood that your yellow cell tables have to have their empty cells.
i reworked the formula with some other tricks and now it is working. i did not touch your other 23 yellow tables they are all intact. please find attached workbook. when any new data pops into the yellow tables. it will automatically pop into the Main Sheet Table24
plz let me know how it goes. hopefully this is the final wrap up
EE.xlsb
ASKER
Hi!
Amazing work! What a formula!
One thing, though. I attach the spreadsheet again, with every person put in, to see what it does - but there are two things:-
(1) The 'Lee Brown' entry does not show up on your 'MAIN' worksheet when i enter it on the 'Timesheet entries' worksheet, and (2) the spreadsheet now works very slowly. That maybe doesn't matter too much - and I guess it might be because your formula is HUGE - but is it also because it's now in .xlsb format?
But thanks for your amazing formula!!!
Lat me know what you think.
Thanks again!
EE-test---151219.xlsb
Amazing work! What a formula!
One thing, though. I attach the spreadsheet again, with every person put in, to see what it does - but there are two things:-
(1) The 'Lee Brown' entry does not show up on your 'MAIN' worksheet when i enter it on the 'Timesheet entries' worksheet, and (2) the spreadsheet now works very slowly. That maybe doesn't matter too much - and I guess it might be because your formula is HUGE - but is it also because it's now in .xlsb format?
But thanks for your amazing formula!!!
Lat me know what you think.
Thanks again!
EE-test---151219.xlsb
@BlosMusic
you are most welcome. i always like to answer challenging questions and go beyond the expectation.
anyone could answer simple questions like vlookup ;-)
thanks for spotting the 'Lee Brown' glitch. i fixed the formula it was missing addtional parathesis. so it is fixed in the attached version.
the slowness is of calculation is due to the formula being in hundreds of rows, so i am affraid you have to live with it :-)
here is the final formula that resides in cells B3 to E4696
=IFERROR(IFERROR(INDEX(Tab
EE.xlsb
you are most welcome. i always like to answer challenging questions and go beyond the expectation.
anyone could answer simple questions like vlookup ;-)
thanks for spotting the 'Lee Brown' glitch. i fixed the formula it was missing addtional parathesis. so it is fixed in the attached version.
the slowness is of calculation is due to the formula being in hundreds of rows, so i am affraid you have to live with it :-)
here is the final formula that resides in cells B3 to E4696
=IFERROR(IFERROR(INDEX(Tab
EE.xlsb
ASKER
Wow!
Mind-boggling!
I want to award at least 10,000 points for this - I have never seen a formula so huge! And it does just what we want.
Just one last thought. If we add more projects, and more employees - how do we adapt your colossal formula? Is that something we could do? I mean, can the formula be extended even further so that as and when we add projects and employees, the formula still works - up to say another 20 projects (or more) and another 10 employees (or is that just an 'ask' too far?). Or are we then into VBA?
Mind-boggling!
I want to award at least 10,000 points for this - I have never seen a formula so huge! And it does just what we want.
Just one last thought. If we add more projects, and more employees - how do we adapt your colossal formula? Is that something we could do? I mean, can the formula be extended even further so that as and when we add projects and employees, the formula still works - up to say another 20 projects (or more) and another 10 employees (or is that just an 'ask' too far?). Or are we then into VBA?
Thanks .
Currently it covers 23 tables with each of them more than 200 rows. So if you need to add additional row , it would be no problem it will dynamically add it but you need to expand the range further down from 4696 rows
This part is easy and it does not require the formula to change just copy the formula down below 4696 rows
But if you would need to add additional tables then the formula needs to change in that case either you try changing it based on the logic I explained or otherwise you can open a question and we would try to help
I think formula still prevails from vba
Because it is dynamic and is fast and it can still work of you even double the data.
Currently it covers 23 tables with each of them more than 200 rows. So if you need to add additional row , it would be no problem it will dynamically add it but you need to expand the range further down from 4696 rows
This part is easy and it does not require the formula to change just copy the formula down below 4696 rows
But if you would need to add additional tables then the formula needs to change in that case either you try changing it based on the logic I explained or otherwise you can open a question and we would try to help
I think formula still prevails from vba
Because it is dynamic and is fast and it can still work of you even double the data.
ASKER
Hi Prof.
Sorry to be a long time answering. I have studied your formula, and whilst I don't understand it as such, I have managed (I thought, anyway) to work out its logic and apply it to more tables. I have extended my spreadsheet to give me 28 tables, and I worked through it all meticulously, but when I extend your formula in the 'MAIN' worksheet, it throws up an error that takes me to a VBA page - which I don't understand. I hadn't understood that there was a lot of VBA behind it all!
Maybe it's something simple - maybe not. Any ideas?
If you want this question closed off (and I will give it at least 10,000 points!) then that's OK - but I am a bit stuck again. So near and yet so far . . .
Input-hours--Sylvia----final-versio.xlsb
Sorry to be a long time answering. I have studied your formula, and whilst I don't understand it as such, I have managed (I thought, anyway) to work out its logic and apply it to more tables. I have extended my spreadsheet to give me 28 tables, and I worked through it all meticulously, but when I extend your formula in the 'MAIN' worksheet, it throws up an error that takes me to a VBA page - which I don't understand. I hadn't understood that there was a lot of VBA behind it all!
Maybe it's something simple - maybe not. Any ideas?
If you want this question closed off (and I will give it at least 10,000 points!) then that's OK - but I am a bit stuck again. So near and yet so far . . .
Input-hours--Sylvia----final-versio.xlsb
BlosMusic
i checked your file. your formula is correct. the formula has nothing to do with the VBA.
the VBA is only used to automatically hide the blank rows in the MAIN table and unhide if there is a Non-Blank. the reason it was throughing error becuase your MAIN sheet table name was Table24 and you changed it to Table30
hence when you get the error and you click on Debug button it will take you to highlighted in yellow line
With ActiveWorkbook.Worksheets(
simply you change the Table24 to Table30 like this
With ActiveWorkbook.Worksheets(
and you are all set and there will be no error.
i have also attached the corrected/modified workbook for your easy reference
Input-hours--Sylvia----final-versio.xlsb
i checked your file. your formula is correct. the formula has nothing to do with the VBA.
the VBA is only used to automatically hide the blank rows in the MAIN table and unhide if there is a Non-Blank. the reason it was throughing error becuase your MAIN sheet table name was Table24 and you changed it to Table30
hence when you get the error and you click on Debug button it will take you to highlighted in yellow line
With ActiveWorkbook.Worksheets(
simply you change the Table24 to Table30 like this
With ActiveWorkbook.Worksheets(
and you are all set and there will be no error.
i have also attached the corrected/modified workbook for your easy reference
Input-hours--Sylvia----final-versio.xlsb
ASKER
Prof.
I tried it but there still seems to be a problem. If I add a new Employee under the worksheet 'Drop-down data' (let's call it "aaa") - and if I also add a Payroll number (again, in the 'Drop-down data' worksheet - say "111"), then this now appears as a drop-down box choice in the 'Timesheet entries' worksheet (as it should) - and any entries I put in for "aaa" then work their way through to the 'Emply.24' worksheet (as it should). But it doesn't appear in the 'MAIN' worksheet! Then I found that the Table in 'Emply.24' worksheet cannot be named 'Table 24' because "Table 24" is already in use! But it isn't in use as far as I can see. So I think your magnificent formula is not picking up anything from "Table 24" because it doesn't yet exist, and i cannot call it "Table 24". The same applies to the worksheets 'Emply.25' right through to 'Emply.28'. I think I need to get the tables in these worksheets re-numbered, but I can't.
Prof., I fully understand if you are a bit fed up with this (and me) now - but it seems to be so nearly there!
Input-hours--Sylvia----final-versio.xlsb
I tried it but there still seems to be a problem. If I add a new Employee under the worksheet 'Drop-down data' (let's call it "aaa") - and if I also add a Payroll number (again, in the 'Drop-down data' worksheet - say "111"), then this now appears as a drop-down box choice in the 'Timesheet entries' worksheet (as it should) - and any entries I put in for "aaa" then work their way through to the 'Emply.24' worksheet (as it should). But it doesn't appear in the 'MAIN' worksheet! Then I found that the Table in 'Emply.24' worksheet cannot be named 'Table 24' because "Table 24" is already in use! But it isn't in use as far as I can see. So I think your magnificent formula is not picking up anything from "Table 24" because it doesn't yet exist, and i cannot call it "Table 24". The same applies to the worksheets 'Emply.25' right through to 'Emply.28'. I think I need to get the tables in these worksheets re-numbered, but I can't.
Prof., I fully understand if you are a bit fed up with this (and me) now - but it seems to be so nearly there!
Input-hours--Sylvia----final-versio.xlsb
@BlosMusic
Happy New Year!
the reason your formula was not working: becuase your formula recognizes the sequence of table 24, 25 26 while your newly created tables had different table names.
the reason you were not able to rename the tables to correct table sequence like Table24 were becuase A) you already had the formula active in mainsheet which used the table24 in its formula. Excel will not allow if you already used a tablename in a formula even though if that table do not exists, becuase usually users create tables first before creating the formulas. B) you had used named ranges which also had broken reference #REF and also were global linked to all sheets.
to be able to rename the new tables, i had to wipe all of the formulas from Main sheet and delete the named ranges and then saved the workbook and reopened and then i was able to rename and i reentered the formula back in Main sheet and copied down and i expanded the table30 to more than 7000 rows in order for the table to be able to encompass the newly created tables, as each of the new tables have 201 rows
please find attached workbook.
in the attached workbook, i have renamed the new tables to tables 24 25 26 27 28
now this is how your tables are named.
TableName Address
Table30 MAIN $B$2:$F$7130
Table1 A Jerrum file $AB$3:$AE$201
Table2 A Short file $AB$3:$AE$201
Table3 C Found file $AB$3:$AE$201
Table4 D White file $AB$3:$AE$201
Table5 D McEwan file $AB$3:$AE$201
Table6 D Drewell $AB$3:$AE$201
Table7 Iain Frowen $AB$3:$AE$201
Table8 James Jones $AB$3:$AE$201
Table9 Lee Brown $AB$3:$AE$201
Table10 Luke Payne $AB$3:$AE$201
Table11 Matthew Bushell $AB$3:$AE$201
Table12 Mike Brooke $AB$3:$AE$201
Table13 Neil Burley $AB$3:$AE$201
Table14 Paul Furley $AB$3:$AE$201
Table15 Philip Evans $AB$3:$AE$201
Table16 Raul Alvarez $AB$3:$AE$201
Table17 Richard Cherry $AB$3:$AE$201
Table18 Rob Challingsworth $AB$3:$AE$201
Table19 Sylvia Willis $AB$3:$AE$201
Table20 Stephen Brown $AB$3:$AE$201
Table21 Tyler Jerrum $AB$3:$AE$201
Table22 Jordan Hutchings $AB$3:$AE$201
Table23 Stanley Roy Jones $AB$3:$AE$201
Table24 Emply.24 $AB$3:$AE$201
Table25 Emply.25 $AB$3:$AE$201
Table26 Emply.26 $AB$3:$AE$201
Table27 Emply.27 $AB$3:$AE$201
Table28 Emply.28 $AB$3:$AE$201
now, with the attached file, you might wanna do a final check on the formula and also for those previously named ranges
EMPLOYEE
PAYROLL_NUMBER
PROJECT_NAME
PROJECT_NUMBER
which i have deleted that had broken reference, they maybe used in your other formulas, so you may want to recreate them if you want to use the attached version.
Input-hours--Sylvia----final-versio.xlsb
Happy New Year!
the reason your formula was not working: becuase your formula recognizes the sequence of table 24, 25 26 while your newly created tables had different table names.
the reason you were not able to rename the tables to correct table sequence like Table24 were becuase A) you already had the formula active in mainsheet which used the table24 in its formula. Excel will not allow if you already used a tablename in a formula even though if that table do not exists, becuase usually users create tables first before creating the formulas. B) you had used named ranges which also had broken reference #REF and also were global linked to all sheets.
to be able to rename the new tables, i had to wipe all of the formulas from Main sheet and delete the named ranges and then saved the workbook and reopened and then i was able to rename and i reentered the formula back in Main sheet and copied down and i expanded the table30 to more than 7000 rows in order for the table to be able to encompass the newly created tables, as each of the new tables have 201 rows
please find attached workbook.
in the attached workbook, i have renamed the new tables to tables 24 25 26 27 28
now this is how your tables are named.
TableName Address
Table30 MAIN $B$2:$F$7130
Table1 A Jerrum file $AB$3:$AE$201
Table2 A Short file $AB$3:$AE$201
Table3 C Found file $AB$3:$AE$201
Table4 D White file $AB$3:$AE$201
Table5 D McEwan file $AB$3:$AE$201
Table6 D Drewell $AB$3:$AE$201
Table7 Iain Frowen $AB$3:$AE$201
Table8 James Jones $AB$3:$AE$201
Table9 Lee Brown $AB$3:$AE$201
Table10 Luke Payne $AB$3:$AE$201
Table11 Matthew Bushell $AB$3:$AE$201
Table12 Mike Brooke $AB$3:$AE$201
Table13 Neil Burley $AB$3:$AE$201
Table14 Paul Furley $AB$3:$AE$201
Table15 Philip Evans $AB$3:$AE$201
Table16 Raul Alvarez $AB$3:$AE$201
Table17 Richard Cherry $AB$3:$AE$201
Table18 Rob Challingsworth $AB$3:$AE$201
Table19 Sylvia Willis $AB$3:$AE$201
Table20 Stephen Brown $AB$3:$AE$201
Table21 Tyler Jerrum $AB$3:$AE$201
Table22 Jordan Hutchings $AB$3:$AE$201
Table23 Stanley Roy Jones $AB$3:$AE$201
Table24 Emply.24 $AB$3:$AE$201
Table25 Emply.25 $AB$3:$AE$201
Table26 Emply.26 $AB$3:$AE$201
Table27 Emply.27 $AB$3:$AE$201
Table28 Emply.28 $AB$3:$AE$201
now, with the attached file, you might wanna do a final check on the formula and also for those previously named ranges
EMPLOYEE
PAYROLL_NUMBER
PROJECT_NAME
PROJECT_NUMBER
which i have deleted that had broken reference, they maybe used in your other formulas, so you may want to recreate them if you want to use the attached version.
Input-hours--Sylvia----final-versio.xlsb
ASKER
Happy
Happy New Year Prof!
I can't test your sheet because the drop-down box on the 'Timesheet Entries' worksheet doesn't seem to work!
Happy New Year Prof!
I can't test your sheet because the drop-down box on the 'Timesheet Entries' worksheet doesn't seem to work!
BlosMusic
Happy New Year!
i have fixed your workbook's dropdowns too. please find attached.
Input-hours-SylviaFINAL.xlsb
Happy New Year!
i have fixed your workbook's dropdowns too. please find attached.
Input-hours-SylviaFINAL.xlsb
@BlosMusic
is there anything else i can help you with?
is there anything else i can help you with?
ASKER
Hello Professor.
Sorry, I have not been well for a few days, and couldn't answer.
The spreadsheet works fine now, and if I could I would give 100,000 points for all the help you have given me, then I would. It was fantastic, and I am very, very appreciative.
There is one thing, though, before we finish. The spreradsheet works very slowly, as there is such a lot of data to consume!
And I now realize that I don't need 198 rows in each of the 29 tables. If we reduced the number of rows right down to say 22 (which is our consensus on the maximum number of normal operations for any employee to undertake in a particular period of time) - would this speed the spreadsheet up? If so, is it easy to reduce the Table rows from 198 right down to 22? That would mean that the 'MAIN' worksheet would need a maximum of (say) 35 employees, each with 22 operations, meaning a 'MAIN' table (the final table) of 35 x 22 = 770 rows, reduced right down from the thousands of rows at present.
Will this help, and can it be done?
But, until now, I can only say thank you, and I am very, very impressed with your knowledge and help.
Blosmusic
Sorry, I have not been well for a few days, and couldn't answer.
The spreadsheet works fine now, and if I could I would give 100,000 points for all the help you have given me, then I would. It was fantastic, and I am very, very appreciative.
There is one thing, though, before we finish. The spreradsheet works very slowly, as there is such a lot of data to consume!
And I now realize that I don't need 198 rows in each of the 29 tables. If we reduced the number of rows right down to say 22 (which is our consensus on the maximum number of normal operations for any employee to undertake in a particular period of time) - would this speed the spreadsheet up? If so, is it easy to reduce the Table rows from 198 right down to 22? That would mean that the 'MAIN' worksheet would need a maximum of (say) 35 employees, each with 22 operations, meaning a 'MAIN' table (the final table) of 35 x 22 = 770 rows, reduced right down from the thousands of rows at present.
Will this help, and can it be done?
But, until now, I can only say thank you, and I am very, very impressed with your knowledge and help.
Blosmusic
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ProfessorJimJam.
Perfection!
I cannot thank you enough! I think we're done. Thank you so much for your help.
Wonderful.
Best regards,BlosMusic
(Richard Cherry)
Perfection!
I cannot thank you enough! I think we're done. Thank you so much for your help.
Wonderful.
Best regards,BlosMusic
(Richard Cherry)
ASKER
Very, very good. I have been very impressed by the help I have been given by ProfessorJimJam, who worked way beyond what I expected.
It has been SO useful, and I give my thanks to him.
He deserves at least 10,000 points! :-)
BlosMusic
It has been SO useful, and I give my thanks to him.
He deserves at least 10,000 points! :-)
BlosMusic
BlosMusic
You are most welcome. I am happy that I was able to help.
You are most welcome. I am happy that I was able to help.