Use cell to determine column in formula

J

JeffC

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
 
R

Roger Govier

Hi Jeff

On your TB sheet set up a named range.
Insert>Name>Define>Name> 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
 
J

Jacob Skaria

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")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top