How would I refer to a formula in a cell rather than the value the formula gives?

**gypsyeyes** · 07-09-2008, 02:30 PM

Hello,
I am stuck.
I have a series of data that I want to put into a fixed region in a table fomat (96 numbers altogether in an 8 x 12 table) this table will be at the top of the page.

My template below this is a numbering table which will arrange the values in the above described table into columns of four.

For example. If the top table is numbered sequentially from 1-96, 1-8 will go down the first column with the top of the next column starting at 9...etc.

Then for the bottom table, I would like 1-4 in one column, followed by 5-8 in the next column and so forth. Additionally, the bottom template will not always start with number one. I intend to have the bottom template number from one to 40, print out the template, and start another at 41-80, etc. (the numbers wont be 1-96, I just indicated this for simplicity sake)

Is there a way to somehow refer to the formula in the bottom template, rather than the value the formula gives? Example, ,the 1st cell in the bottom template (which is actually cell A16) would be = A1 (first cell in first template). (Thats the easy part) can I somehow make a formula in my bottom template tells the program to indicate the value of the cell in a1, then give the value of the cell right below it, etc? Sort of an auto numbering template based on the top 96 cell data set?
Hopefully I dindt word that too confusing-ly

Thanks

**Richard Buttrey** · 07-09-2008, 07:13 PM

Hi, and welcome to the forum.

Sounds like you want something like:

=OFFSET(A1,1,0)

where the '1' is a row offset, and the '0' is the column offset. The above refers to cell A2.

Similarly =OFFSET(D10,-5,-2) would refer to B5.

Depending on your exact requirements, you might need to calculate these row/column offsets via other functions, like =MATCH() etc.

Upload your workbook if you're still stuck. It's always easier if we can see these problems in context.

Rgds

**gypsyeyes** · 07-10-2008, 09:13 AM

Thanks so much!

**gypsyeyes** · 07-10-2008, 11:47 AM

Hi,
I am using the Offset function with great success (=OFFSET($D$7,0,0), then =OFFSET($D$7,1,0) etc. My problem now is that my second template table will not always start at $D$7, sometimes it will be at $E$7 or at $F$7. Is there someway to have all these formulas in a second template such that the offset function will always refer to the specified cell? Example =OFFSET($variable cell reference$,0,0) etc.
Thanks,
Leanne

**Richard Buttrey** · 07-10-2008, 01:13 PM

Hi,

You could try using named cells, or as I intimated in my first response, you might be able to work out where the start cell is. Usually this is the case where you want to pick a particular date in a row of dates. You'd use something like

=MATCH(date,E6:Z6,False)

This would return a column number which you could then use as an offset from a fixed point.

If you want to upload a copy of your workbook, along with a few notes with reference to relevant cells, it would be easier to advise when we can see the task in context.

Rgds

**gypsyeyes** · 07-10-2008, 08:46 PM

I've attached a worksheet to try to explain better. Thanks again so much for your help!!

**Richard Buttrey** · 07-11-2008, 04:46 AM

Hi,

Four questions.

You say that the same number may appear several times in the table. Do you want the template to contain any duplicate numbers, or must they all be sequential?

If you require only 40 numbers, why do you choose to have a template which contains 44 places?

Can the 40 number template be filled sequentially left to right, or do you always want the central column F to be empty rather than the 11th column K?

Can the template be filled with the first 40 numbers, starting with the first column and going down the rows then moving across to the 2nd column, then 3rd..etc.?

In the meantime I'm re-attaching your workbook with a work in progress solution based on what I currently understand. We can modify it in accordance with the answers to the questions above. Just click the appropriate button to fill the new template range which I've started at A27

Rgds

**gypsyeyes** · 07-13-2008, 10:14 PM

Hello Again,
Thank you for being so patient with my big and confusing request.

to answer your questions:

Can the template be filled with the first 40 numbers, starting with the first column and going down the rows then moving across to the 2nd column, then 3rd..etc.?
Yes the table may contain dupl;icate numbers, they are for samples so one group of samples may be labelled A(1-6) then the second set of samples B(780-802) and the 3rd set C-(1-19) where the letters indicate the group number. I didnt want to indicate group number on my printable worksheet as that is not the format that is acceptable, rather I would go back and physically write in the group number above the appropriate numbers

If you require only 40 numbers, why do you choose to have a template which contains 44 places?
The 4 places empty that are in the middle are for controls, that is, they will always contain the same values (we can call them A B C D)

Can the 40 number template be filled sequentially left to right, or do you always want the central column F to be empty rather than the 11th column K?

See above

Can the template be filled with the first 40 numbers, starting with the first column and going down the rows then moving across to the 2nd column, then 3rd..etc.?

yes

and, what I would like to do is fill in the first 40 numbers of the template, print off the worksheet as sheet 1, then go to the next 40 numbers, etc. To make matters more confusing, when I get to the end (the 96th value) My worksheet would only have 26 numbers on it so I would then have to start over with a NEW top template, filled with new samples, to complete the bottom 40 sample template before printing off. Please see attached for what this would actually look like...

Therefore, what I would really like is a formula that says (start at cell x) and fills in 39 numbers after that going down the columns.
Ive been wracking (sp) my poor brain!!
if all else fails I could probably just rearrange the template into 4 row columns and cut and paste these into the template.
Thanks again so much for your help!!! It is very very greatly appreciated!