Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() 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 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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:D,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. -- Regards Roger Govier wrote in message ... 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 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Assumptions:
You data is located from A1:D3. 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 " wrote: 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 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Jan 29, 10:46*am, wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
problems trying to combine two sheets | Excel Discussion (Misc queries) | |||
Copying only report sheets | Excel Discussion (Misc queries) | |||
Combine to Sheets... | Excel Discussion (Misc queries) | |||
HELP!!! combine 4 different sheets ??? | Excel Discussion (Misc queries) | |||
How to Build an Excel-Report ? | Excel Discussion (Misc queries) |