Combine sheets to build a report

[mcgrathml]

Hi,

Trying to combine a worksheet with rows of data with a "formal"
Excel report (separate sheet).


The data work sheet looks like

A B C D
Lab ID temp Pressure colour
1 .05 .09 Red
2 .06 .07 Blue
100-200 more rows etc.

The report looks like


LabID: 1

Temp: .05

Press .09

Colur Red

(the readings are always in the same cell).


The functionality needed is

Allow customer chooses a lab_id (using a form?)
The cells of the report are populated with the values for that labID.
The customer prints the report or specifies another labID

I was advised VLOOKUP could do this but don't really see how.
Hoping to avoid VBA if possible.

All help greatly appreciated.

Michael

 

[Roger Govier]

Hi

Assuming your report is on sheet2 with Titles in cells A1:A4 and Values in
B1:B4.
The user enters the lab number in B1
in B2
=IF(B1="","",VLOKUP(B$1,Sheet1!A,ROW(A2),0))
Copy down through cells B3:B4

Note. If you space out your report and use blank lines between successive
entries, then you would not use the ROW() function, but you would need to
manually enter the number 2 in the first formula, 3 in the next and 4 in the
final one.

 

[Vinay]

Assumptions:
You data is located from A13. Four columns, one each for Lab ID, Temp,
Pressure and Color.
Your report is located from A6:B9. The labels in column A, the data to
appear in column B

Solution:
1. Insert a combo box. The input range for the combo box will be A2:A3.
Basically the list of lab ids. Cell link for the combo box will be B6. You
can access these properties on the Control tab on the Format control dialog.
Then place the combo box over cel B6.

2. Use the following VLookUp funtion to populate the values based on the lab
id selected from the combo box: =VLOOKUP($B$6,$A$1:$D$3,2). This particular
function brings up the temperature. Replace "2" with "3" for Pressure, and
"with "4" for Color in their respective cells.

Vinay

 

[mcgrathml]

Hi,

        Trying to combine a worksheet with rows of data with a "formal"
Excel report (separate sheet).

The data work sheet looks like

A               B               C                   D
Lab ID          temp            Pressure      colour
1               .05             .09                Red
2               .06             .07                Blue
                 100-200 more rows etc.

The report looks like

LabID:    1

Temp:     .05

Press       .09

Colur        Red

(the readings are always in the same cell).

The functionality needed is

        Allow customer chooses a lab_id (using a form?)
        The cells of the report are populated with the values for that labID.
        The customer prints the report or specifies another labID

I was advised VLOOKUP could do this but don't really see how.
Hoping to avoid VBA if possible.

All help greatly appreciated.

                  Michael

Roger, Vinay,

Thank you to both of you I'll get back to work!

Michael