Creating a template that is manually updated from another sheet.

[MKruer]

Usually I am pretty good at using formulas, but this one has me
baffled, I am pretty sure it is possible.

I have two sheets; on the first page I have all my calculations and
variables, one row per item, but multiple columns. On the second
sheet I have how the items should be formatted. I am trying to figure
out a way script it so I can choose a row, and have it fill in the
cells on sheet 2, or choose another row from sheet 1 and have it
recalculate the cells on the sheet 2. In a nutshell sheet 2 is a
template that gets it information form sheet one. The trick is that I
need to be able to choose what rows from the first sheet.

 

[John C]

Well, without knowing your data structure at all, and without knowing how you
are obtaining which specific row, you could do something like this:
Assuming A1 contains the row that you need.

B1: =INDIRECT("Sheet1!B"&A1)

If this doesn't get you on the right track, post back with more info, such
as data structure, formulas, and expected results.

 

[MKruer]

Well, without knowing your data structure at all, and without knowing howyou
are obtaining which specific row, you could do something like this:
Assuming A1 contains the row that you need.

B1: =INDIRECT("Sheet1!B"&A1)

If this doesn't get you on the right track, post back with more info, such
as data structure, formulas, and expected results.

 

[MKruer]

This is a small fictional data set, but it should be able to clarify
what I am looking for. The problem is that I need to be able to choose
which row i want the formula to use, but i don't want to have to go to
each and every cell to update the formula to use the new row.

Example

:Column A, Column B, Column C, Column D,
Row 1: 25, 49, apple, orange
Row 2: 10, 100, pear, lemon

If I select (this is the problem that I am having) the first row the
output should be
Amount on hand = 25
Amount required = 49
Item = apple
Substitute Item = orange

If i select the second row the output would be
Amount on hand = 10
Amount required = 100
Item = pear
Substitute Item =lemon

 

[John C]

Okay, assuming your other tab is called Produce, and like you stated, your
data actually begins in column A, and row 1 (i.e.: no headers). Then on your
main tab, you need to select which row. If you are selecting by row number,
then use the following for each. I am using cell B2 as the reference on my
main tab for which row number I want to use, so your formulas would be as
follows.

Amount on Hand:
=IF(OR($B$2="",$B$2<COUNT(Produce!A:A)),INDIRECT("Produce!A"&$B$2),"")

Amount Required:
=IF(OR($B$2="",$B$2<COUNT(Produce!A:A)),INDIRECT("Produce!C"&$B$2),"")

Item:
=IF(OR($B$2="",$B$2<COUNT(Produce!A:A)),INDIRECT("Produce!C"&$B$2),"")

Substitute Item:
=IF(OR($B$2="",$B$2<COUNT(Produce!A:A)),INDIRECT("Produce!D"&$B$2),"")

Hope this helps.

 

[MKruer]

Thats exactly what I was looking for. Thank you.

Okay, assuming your other tab is called Produce, and like you stated, your
data actually begins in column A, and row 1 (i.e.: no headers). Then on your
main tab, you need to select which row. If you are selecting by row number,
then use the following for each. I am using cell B2 as the reference on my
main tab for which row number I want to use, so your formulas would be as
follows.

Amount on Hand:
=IF(OR($B$2="",$B$2<COUNT(Produce!A:A)),INDIRECT("Produce!A"&$B$2),"")

Amount Required:
=IF(OR($B$2="",$B$2<COUNT(Produce!A:A)),INDIRECT("Produce!C"&$B$2),"")

Item:
=IF(OR($B$2="",$B$2<COUNT(Produce!A:A)),INDIRECT("Produce!C"&$B$2),"")

Substitute Item:
=IF(OR($B$2="",$B$2<COUNT(Produce!A:A)),INDIRECT("Produce!D"&$B$2),"")

Hope this helps.

 

[John C]

You are welcome, and thanks for the feedback. Please remember to check the
YES box below so that others can know this is done.