is there anyway to take a spread sheet and have another sheet reference 1a 1b 1c 1d and then copy that sheet and have the second one reference 2a 2b 2c 2d automatically ?

i have a spread sheet of a ton of data that i need to enter into individual forms and i don't want to do it by hand.

3/26/2007 7:21:36 AM

que?

i'm sure there is, but i'm not following you....

3/26/2007 7:25:37 AM

ok say sheet 1 is a large data set

and sheet 2 is a form that does some math based on sheet 1s large data set is there any way to make

sheet 2 get values from sheet 1 row 1 and sheet 3 get values from sheet 1 line2 and sheet 4 get values from sheet 1 line 5 ...........................................


sheet 2 and all subsiquent sheets are identical forms doing math on different lines of data from sheet 1




[Edited on March 26, 2007 at 7:37 AM. Reason : .]

3/26/2007 7:35:57 AM

hmm, ok.
how many sheets are there? are you willing to go through every sheet, at least one time, to fill in a formula on each sheet?
Do Sheets 2:x contain other data or formulas that need to be maintained? i.e. can you paste a formula into every cell in the whole sheet, or will that destroy the data that is already there to make the form?

can you add additional data to the other sheets that would help with the calculation? i'm asking because i'm thinking of something using the INDEX() formula where each sheet has an incremental index number that corresponds to the row in the first sheet where it should take its data....

[Edited on March 26, 2007 at 7:47 AM. Reason : .]

3/26/2007 7:45:47 AM

for this project there are.... 89 sheets


and i would rather not.
but i can.

3/26/2007 7:49:24 AM

are the sheets named arbitrarily, or are they numbered?

3/26/2007 8:00:28 AM

i'm still trying to think of a way to do this quickly using normal excel funcitons, but of course it could be done in VBA. The tricky part there is that each sheet would need to be "self aware" of what number it was, either inherintly by using a formula (which i don't think is possible) or by explicity adding a label to each sheet (like adding a number in A1 of each sheet)

in VBA, excel sheets can be referenced by index number, like Sheets(2), so a loop like this would work (in misformed vba)

For i=2 to 89
Sheet(i).Range(cellnumber).FormulaR1C1 = "index(Sheet1!A1:M14,i-1,columnNumber);
End For

3/26/2007 8:22:44 AM

thanks for trying guys,

i ended up doing 2 sheets by hand and then making 4 excel files with each of the 4 sections at the top

3/26/2007 12:36:49 PM

for future reference, if you have access to matlab, you can do this pretty quickly. first do all the calculations in an m-file and then write from matlab directly to excel. you can specify in matlab the excel filename, sheetname, and column/rows. then apply some pre-recorded macros for formatting and graphing, if necessary.

3/26/2007 10:11:36 PM