I've Created DV Boxes - Need Additional Help Linking Data to them

[Kellie]

I have a budget worksheet that has 3 different companies income/expenses
included to do a combination budget vs actual costs. I created the Data
Validation drop-downs to include the month by month data, however I can't
figure out how to link the actual cells from each month to the drop down
month. For instance, when you click on January from the drop down in company
a, I want the expenses from January on another worksheet to appear and so on.
How do I do that?

 

[Patrick Molloy]

many ways to do this.
on sheet1
create a table, range named Expenses with two columns, first is a list of
months, second col the sheet& cell for the first item, so for me January is
Sheet2!B2, February Sheet3!B2 and so on
In B2 I have my cell validation, with drop downs for each month
in B4 I get the first items cell from the lookup
=VLOOKUP($B$2,Expenses,2,FALSE)

In B6 I get the value, using ROW() for my offset, which is the date
B6:= =OFFSET(INDIRECT($B$4),ROW()-6,0)

And B7 is the next column, the amount
B7:= =OFFSET(INDIRECT($B$4),ROW()-6,1)

on sheet2 Jan expenses start at B2, with the date, and C2 is the amount
on sheet3 Feb expenses start at B2, with the date, and C2 is the amount

This should be enough to get you going.
If you want my demo, please mail me directly

 

[AltaEgo]

1) set up named ranges for your lookup in each of your monthly sheets. Use a
two part name when you do this: one part will relate to the month; the other
part will be consistent. Example
January name range "JanuaryList"
February named range = "FebruaryList"
March named range = "marchList"
etc

In you sheet where you select your month, set up a cell somewhere that
combines the value of your month selection and the other part of your named
range.

Example: If you select your month in A2 then the cell formula will be
=A2 & "List" (for the purpose of the example, lets say we do this in C2)

The result after you do this will show the named range depending which month
you select. Example. If you select March C2 should update to "MarchList".

Now your data validation formula becomes:

=INDIRECT($C$2)


The theory in summary,
You need a single cell that updates depending on the month chosen.
Each time this cell changes, it must match a named range (in the relevant
sheet).
Data validation uses the Indirect function to change where it obtains its
information based on the value retrieved from the single cell.

 

[AltaEgo]

For some reason or other, I had in mind a more complex scenario. You can, of
course, skip the cell that combines the listed month with "List" by writing
it direct in the Data validation this:

=INDIRECT(A2 &"List") where A2 is your dropdown list January, February, etc.