Use cell to determine column in formula
 

Hi, I have a worksheet in which I add a new column each month for the new
activity in the trial balance. that information is in one sheet. In
separate sheets, I have cash flow statements (one for each month, meaning 12
separate sheets). At this point I manually transfer totals from the trial
balance to the cash flow statements. I would like to find a way to do the
following: If I could have all the formulas on the cash flow statement point
to the proper row on the trial balance BUT be dependent upon a variable for
their column selection (columns Jan-Dec), then I could actually have just two
sheets in this workbook... The monthly cash flow sheet would work for
Jan-Dec by just changing that variable, which could possibly be the numbers
1-12, or something along those lines. Thanks in advance, any help would be
GREATLY appreciated

Use cell to determine column in formula
 

Hi Jeff

On your TB sheet set up a named range.
InsertNameDefineName TB Refers to =$A$1:$M$100
I assume you have 13 columns, with column A being the heading.
Extend the number of rows to cover the range of your data.

On your Cash Flow sheet, assuming headings again in column A, and values in
column B
Enter in B1 the Month number required

In the first row of data, say row 3 enter in B3
=INDEX(TB,Row(),$B$1+1)
Copy down as far as required
--
Regards
Roger Govier

"JeffC" wrote in message
...
Hi, I have a worksheet in which I add a new column each month for the new
activity in the trial balance. that information is in one sheet. In
separate sheets, I have cash flow statements (one for each month, meaning
12
separate sheets). At this point I manually transfer totals from the trial
balance to the cash flow statements. I would like to find a way to do the
following: If I could have all the formulas on the cash flow statement
point
to the proper row on the trial balance BUT be dependent upon a variable
for
their column selection (columns Jan-Dec), then I could actually have just
two
sheets in this workbook... The monthly cash flow sheet would work for
Jan-Dec by just changing that variable, which could possibly be the
numbers
1-12, or something along those lines. Thanks in advance, any help would
be
GREATLY appreciated

Use cell to determine column in formula
 

Either use Match() function to identify the column number
=MATCH("<month",A1:L1,0)

OR

If the month columns are fixed you can get the column number using the below
function by passing the date.
A1 = date
=TEXT(A1,"m")


--
If this post helps click Yes
---------------
Jacob Skaria


"JeffC" wrote:

Hi, I have a worksheet in which I add a new column each month for the new
activity in the trial balance. that information is in one sheet. In
separate sheets, I have cash flow statements (one for each month, meaning 12
separate sheets). At this point I manually transfer totals from the trial
balance to the cash flow statements. I would like to find a way to do the
following: If I could have all the formulas on the cash flow statement point
to the proper row on the trial balance BUT be dependent upon a variable for
their column selection (columns Jan-Dec), then I could actually have just two
sheets in this workbook... The monthly cash flow sheet would work for
Jan-Dec by just changing that variable, which could possibly be the numbers
1-12, or something along those lines. Thanks in advance, any help would be
GREATLY appreciated