• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Online - Copy Data From Multiple Worksheets into one.

P

Prasad Pande

New Member
Hi Everyone,

I am working on creating a dashboard that pulls data from multiple users working on Independent worksheets using Excel Online web application. Please see the attached excel file to get an idea how the file looks like.

https://dl.dropboxusercontent.com/u/87387378/Sample.xlsx

Now each user adds data in their own sheet at same time and data has to be updated in the "Master" Worksheet after a interval and this consolidated data will be used as source for Pivote.

I know there have been multiple posts about doing this using a VB code and i tried to explore that option, but i am afraid as of now the Online excel does not support Macro's.

I am looking forward to get some help from you guys to solve this problem using standard excel formulae's which will be supported on Online excel.


Regards,
Prasad
 
Hi Prasad ,

Can you indicate :

1. How many tabs will be present in your working file ? What is the upper limit ?

2. How much of data will be present on each sheet tab ? What is the upper limit ?

Narayan
 
NARAYANK991 said:
Hi Prasad ,

Can you indicate :

1. How many tabs will be present in your working file ? What is the upper limit ?

2. How much of data will be present on each sheet tab ? What is the upper limit ?

Narayan
Click to expand...

Hi Narayan,

There will not more than 6 tabs. Each of the tab will not have more than 1500 rows. All data will be flushed at the start of new month.
 
Hi Prasad ,

You will need to remember that a formula-based solution will not remain if you delete rows / columns ; when you clear data , you will have to clear the cells / ranges , not delete the rows / columns.

Narayan
 
Basically i will be creating a new file every month. But yes i get your point formulae need to kept intact. Can you think of a way to do this without Vb ?
 
Hi Prasad ,

See this file ; the formulae are simple for two reasons :

1. The data ranges have been converted to tables ; these tables should not be renamed.

2. Two helper columns have been introduced

Narayan
 

Attachments

NARAYANK991 said:
Hi Prasad ,

See this file ; the formulae are simple for two reasons :

1. The data ranges have been converted to tables ; these tables should not be renamed.

2. Two helper columns have been introduced

Narayan
Click to expand...

Hey,

Looks like you have solved my Problem. Thank you so much. A couple of questions

1. How does the formula in Row Number works ?
2. What changes do i need to make in formula if i have to add more tabs ?

Prasad
 
Hi Prasad ,

The formula will become lengthier !

What the formula does is if the current row number ( ROW() ) less the starting row number ( ROW($A$2) ) + 1 , is less than the number of rows in the first table , just put in the rows from 1 till the number of rows in the first table.

Once we have completed this , now we need to compare the row with the total number of rows in Table1 and Table2 , and restart the numbering from 1 till the number of rows in Table2.

As you add more tables , you will need to incorporate these additional tables in the IF statement.

If you are interested , let me know , and I can put in the formulae for all the 6 or 7 ( at the most ) tables , and upload the file.

Narayan
 
Hi, Prasad Pande!

At the same thread as before, in this link:
http://chandoo.org/forum/threads/me...ets-in-a-master-sheet.11074/page-2#post-84309
there's a 3 child worksheet file doing the same job via formulas.

This is one of the 3 tab formulas:
=IF(ROW()-1<=ROWS(Table1),INDEX(Table1,ROW()-1,COLUMN()),IF(ROW()-1<=ROWS(Table1)+ROWS(Table2),INDEX(Table2,ROW()-1-ROWS(Table1),COLUMN()),IF(ROW()-1<=ROWS(Table1)+ROWS(Table2)+ROWS(Table3),INDEX(Table3,ROW()-1-ROWS(Table1)-ROWS(Table2),COLUMN()),"")))

So I strongly discourage you to follow a formula only path with more than 2 or 3 detail worksheets, formulas will become impractical.

Regards!
 
SirJB7 said:
Hi, Prasad Pande!

At the same thread as before, in this link:
http://chandoo.org/forum/threads/me...ets-in-a-master-sheet.11074/page-2#post-84309
there's a 3 child worksheet file doing the same job via formulas.

This is one of the 3 tab formulas:
=IF(ROW()-1<=ROWS(Table1),INDEX(Table1,ROW()-1,COLUMN()),IF(ROW()-1<=ROWS(Table1)+ROWS(Table2),INDEX(Table2,ROW()-1-ROWS(Table1),COLUMN()),IF(ROW()-1<=ROWS(Table1)+ROWS(Table2)+ROWS(Table3),INDEX(Table3,ROW()-1-ROWS(Table1)-ROWS(Table2),COLUMN()),"")))

So I strongly discourage you to follow a formula only path with more than 2 or 3 detail worksheets, formulas will become impractical.

Regards!
Click to expand...
Hi SirJB7,

Thanks for the link. If not the formula what can be an alternative ? VB does not work with excel online :(
 
Hi, Prasad Pande!
That's a good question and I don't know the answer since I try to avoid using Excel Online, at all if possible. Let me try it and I'll get back to you; I can't believe that VBA doesn't work on online version. But coming from Microsoft nothing would surprise me... except logical things :oops:
Regards!
 
Hi Narayan and SirJB7,

I have modified the formula shared for 4 tabs now and i understood the logic. Now i have a small issue. If you look beyond row 59, its showing errors. If it shows error my pivote will not work. How do i solve this problem ?

Prasad
 

Attachments

Hi Prasad ,

I think you did not read my last post ; if you want me to make your file ready for up to 7 tabs , please let me know.

Narayan
 
You must log in or register to reply here.
Back
Top