Populating worksheet based on multiple worksheets

[FNMGR]

I have 86 worksheets with data that I need to populate an "upload
sheet. Each worksheet will have its own column. I manually did the firs
sheet and really dont want to have to do 85 more.. Is there a macro
can use to do this for me? My file exceeds the max limit here, so i
anyone needs to see it then I can email it or find another way to uploa
it

 

[Auric__]

I have 86 worksheets with data that I need to populate an "upload"
sheet. Each worksheet will have its own column. I manually did the first
sheet and really dont want to have to do 85 more.. Is there a macro i
can use to do this for me? My file exceeds the max limit here, so if
anyone needs to see it then I can email it or find another way to upload
it.

Shouldn't be a problem. Either upload your worksheet to one of the bazillion
filesharing sites, or just post some sample data -- what the 86 worksheets
look like, what the upload sheet looks like, and what data on the 86 needs to
go to the upload.

 

[FNMGR]

Hi,

Do you mean 86 worksheets (i.e. tabs in one workbook) or workbooks (i.e
separate Excel files)?

The solution will very much depend on which you mean.

PM me for an email address if so you can send it for me to look at i
you like.

S.

There are 86 sheets in 1 book.. i will PM yo

 

[Dpocahontas]

There are 86 sheets in 1 book.. i will PM you

Hi,

I am having kind of the same problem, so please could you put th
solution in case there is one...but ok i have only 25 workbooks and
need to pollute them in different worksheet...and i do the first mysel
but is like too much work to do for 50 sheets.

sorry for the interference!

thank you a lot

 

[FNMGR]

Hi,

I am having kind of the same problem, so please could you put th
solution in case there is one...but ok i have only 25 workbooks and
need to pollute them in different worksheet...and i do the first mysel
but is like too much work to do for 50 sheets.

sorry for the interference!

thank you a lot .

I dont really have a solution. I had to manually do the first then
copied the formula as text into the other columns then did
find/replace to relace the "Sheet" name. Then a text to columns to ge
the text to convert back to general

 

[FNMGR]

You shouldn't have had to do all that stuff with text formats. As lon
as the formulas were set for the first sheet and references locked s
the column/row can be dragged sideways/downward (depending on you
setup) you could just use find and replace on each column/row in turn t
change the sheet reference to the next and the next. It's a horri
workaround, but probably easier than the alternative if you only had t
do it once.

The alternative would be writing the formulas using =INDIRECT() t
reference each sheet name with a column/row header. It's easy enough t
do if you know how to use it, but without example sheets I couldn't tel
you how to set it up.

Hope it's of some help at least.

S.

It kept tying to get me to link to a worksheet when i did the formul
then tried to find/replace within the formula. I found it just as eas
to create the first set of formulas, set as text and copy all those int
my columns then do the find/replace. it worked and only took 10 minute
to do 85 sheets so i wasnt complaining muc

 

[Dpocahontas]

Very bizarre. I just tried it on your file and got nothing of th
sort.
I've emailed it back to you so you can give it a try. Perhaps you wer
doing it a different way to me. Not that it matter as you've sorted i
now anyway

Spencer,

I know indirect formula but is not convienient as you might have a lo
of summaries in different ways so it takes time...i don't know if yo
can create a macro if you do your first template manually...maybe is no
possible.

have a look at this thread please just to have an idea!!

http://www.excelbanter.com/showthread.php?t=446032


Thank yo