Pre-Populate based on other inputs

**DHFE** · 02-24-2014, 09:29 AM

Example.xlsx

Using the attached example, i would like the table on the right (Eqpt + ID#) to pre-populate based on the inputs in columns A-F (at the moment there is no formula, it's all manually entered)
The number of inputs could vary, and may not always be in numeric order of columns A-F (there should only be numeric values entered).
A (TA) up to 24 separate inputs
B (PU) up to 24 separate inputs (either single numbers or pairs)
C (FI) up to 10 separate inputs
D (VE) up to 4 separate inputs
E (LC) up to 2 separate inputs
F (OS) up to 2 separate inputs

I would like to make it so that the table on the right ignores any blanks (as shown in the examples)

**Kaper** · 02-24-2014, 09:56 AM

I think that a formula would be quite complicated, while macro works quick and is easy to read:

Sub test()
Dim i As Long, j As Long, k As Long, l As Long
k = 2
l = Cells(Rows.Count, "H").End(xlUp).Row
Range("H2:I" & IIf(l < 2, 2, l)).ClearContents
For i = 1 To 6
  l = Cells(Rows.Count, i).End(xlUp).Row
  If l > 1 Then
    For j = 2 To l
      Cells(k, "H") = Cells(1, i)
      Cells(k, "I") = Cells(j, i)
      k = k + 1
    Next j
  End If
Next i
End Sub

if you want not only valuees but also formatting then instead of

      Cells(k, "H") = Cells(1, i)
      Cells(k, "I") = Cells(j, i)

use
Cells(1, i).copy Cells(k, "H")
Cells(j, i).copy Cells(k, "I")

**ragulduy** · 02-24-2014, 10:23 AM

An alternative formula approach:
=INDEX($A$1:$F$1,7-MATCH(ROW(A1),SUBTOTAL(3,OFFSET($A$2:$A$7,0,0,6,7-COLUMN($A$1:$F$1))),-1))
in H2 as an array formula (confirm with ctrl+shift+enter)
and
=HLOOKUP(H2,A$1:F$7,COUNTIF($H$1:H1,H2)+2,0)
in I2

then copy down

**DHFE** · 02-24-2014, 10:24 AM

@Kaper & @ragulduy

Thanks for the replies, i 'll let you know when i've had a chance to test this. It's for work and i've just been given another task to do.

**DHFE** · 03-03-2014, 09:11 AM

@Kaper & @ragulduy
Thanks, those do both work. However (and this is my fault) only in the format i made the example sheet in. I really thought i'd be able to modify the response to the actual document. Could you have another look it this example for me and see if it can be done?
Example.xlsx

@Kaper - I need the macro to run automatically once data has been entered, is this possible? The people filling in the information will most likely not know how to run it manually.

@ragulduy - your formula only includes the information i provided in the example - as mentioned in my initial post there can be up to 24 entries in PU and TA. I assume this can be done by substituting the "7" in your formula, however with the layout of the new example will this affect the rest?

**ragulduy** · 03-04-2014, 08:15 AM

Hi:

Looking at the formula in H2 first:
=INDEX($A$1:$F$1,7-MATCH(ROW(A1),SUBTOTAL(3,OFFSET($A$2:$A$7,0,0,6,7-COLUMN($A$1:$F$1))),-1))

A1:F1 is to do with column headers, index(a,b) finds the bth term of the range a. So the 7-Match part is to do with columns. As Is the 7-column(A1:F1) part. The two parts relating to rows are in the offset function, the A2:A7 is the initial range and 6 is the number of rows, so for example, if your range went from A2:A14, the formula would become:
=INDEX($A$1:$F$1,7-MATCH(ROW(A1),SUBTOTAL(3,OFFSET($A$2:$A$14,0,0,13,7-COLUMN($A$1:$F$1))),-1))
Where A2:A14 is the range and 13 is the number of rows in the range.

The holookup is a bit easier, you just need to change the lookup range, so for 14 rows
=HLOOKUP(H2,A$1:F$8,COUNTIF($H$1:H1,H2)+2,0)
would become
=HLOOKUP(H2,A$1:F$14,COUNTIF($H$1:H1,H2)+2,0)

Unforunately, this approach is based on your data being in a table on one sheet as you have in your first workbook. Once each heading is moved onto different sheets, I don't think it would be possible to adapt it. You would need to build the table from the data on the separate worksheets first.

**DHFE** · 03-04-2014, 08:18 AM

I suppose I could use a hidden part of the sheet to build the data from the separate ones onto a single page and then use your formula.

Thanks for the help.

**DHFE** · 03-04-2014, 08:19 AM

EDIT:
sorry for the double post, something strange happened to my browser.

Pre-Populate based on other inputs

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Pre-Populate based on other inputs

Re: Pre-Populate based on other inputs

Re: Pre-Populate based on other inputs

Re: Pre-Populate based on other inputs

Re: Pre-Populate based on other inputs

Re: Pre-Populate based on other inputs

Re: Pre-Populate based on other inputs

Re: Pre-Populate based on other inputs

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Values based on three inputs

[SOLVED] Values based on two inputs

Create and Auto-Populate One Multi-Row Worksheet Using Three Worksheets As Inputs

Excel 2007 : Finding a value based on 2 inputs

Populate table based on 3 inputs

Bookmarks

Bookmarks

Posting Permissions