Name selection should populate other cells

[ipbr21054]

Evening.
My information is on the worksheet called INFO
On the INFO worksheet we have the following,
Names in column CV
Address in column CX
Paid in column CZ
Mileage in column DB
Current range on the INFO worksheet is CV2:DB16
Overtime this range will increase down the page.


I have a worksheet called GRASS INCOME & this is where the magic should happen.
I would enter the date in the next available cell & in this case it would be A29
I currently have drop down lists in each cell but looking for a better approach to populate each cell.
So i select the customers name from the drop down list in cell B29
Now what i would like is for the following cells to populate automatically by using the information on my INFO sheet.
Like so,
ADDRESS C29
PAID D29
MILEAGE E29

So i would type TOM JONE & i would then see 123 ANY STREET £50.00 6
This would then continue down the page so next input would be row A30

Thanks very much

 

[kweaver]

Excel 2010
	A	B	C	D	E
1	Date	Name	Address	Paid	Mileage
2	7/13/2019	Brown	3 Main	300	70
GRASS INCOME

Cell Formulas
Range	Formula
C2	=VLOOKUP($B2,Sheet7!$CV$2:$DB$9,3,FALSE)
D2	=VLOOKUP($B2,Sheet7!$CV$2:$DB$9,5,FALSE)
E2	=VLOOKUP($B2,Sheet7!$CV$2:$DB$9,7,FALSE)

 

[kweaver]

Change the sheet7 to your sheet name (e.g., INFO). Also, modify the date format and the currency symbol as needed.

You'll need to change the 9 in the formula to a large'ish number to cover eventually adding more names.
Alternatively, you can calculate the last row, but then the formula will involved INDIRECT, if you don't care.

=VLOOKUP($B2,INDIRECT("Sheet7!$CV$2:$DB$"&COUNTA(Sheet7!$CV:$CV)),3,FALSE)

 

[ipbr21054]

Hi,
Now applied and working thanks.

Tomorrow i will need to look into altering the code so that cells that do not have a customer yet selected does not then show #N/A in column C,D & E

Also in cell D42 i also see #N/A
I have this formula in the cell =SUM(D5:D41)

Also in cell E42 ia lso see #N/A
I have this formula in the cell =SUM(E5:E41)

I assume once the above cells do not show #N/A then cell D42 & E42 will show the total in figures correctly

Thanks

 

[kweaver]

You can always wrap around an iferror:

=IFERROR(VLOOKUP($B2,INDIRECT("Sheet7!$CV$2:$DB$"&COUNTA(Sheet7!$CV:$CV)),3,FALSE),"")

You never explained what's in D or E columns.

 

[ipbr21054]

Now changed and works perfect