Pulling data, from sheets if a certain name is selected in dropdown

[Caliberjoe]

I have been searching for the answer to this question and am coming up emty handed. I am creating an excel workbook for my consignment store to help me keep track of sales for the store as well as the consignors. I have a sheet for each day of the month, and in the sheets I have it to where I can select the consignor from a drop down box. I am trying to create a sheet at the end of the workbook that would allow me to use the drop-down list to select a consignor and have it pull the sales for the month onto that sheet, an end of the month summary of sales. I don't mind creating a formula for each day. IM taking my time on this to make it work the way I want it to. But I cannot figure out how to make it reference that through the drop down box. Consignors name may not be in the same spot each day and might not have sales for that day.

At first I was thinking an IF formula, but cannot figure out how to do an IF for a range of cells from one sheet and have it pull the sales from that same sheet.

I cannot figure this out. I hope I am explaining what I am trying to do enough, and If I'm not please let me know and tell me how I can explain it better.

please help! Thank you!

 

[AliGW]

You need to create the drop-down list first using data validation - the easiest way is to use a hidden tab in the worksheet that lists the days of the month and reference this in the drop-down list. Next you need to adapt all the formulae in the summery tab to reference the cell with the drop-down box using the INDIRECT function. So, if you want to return the value in A1 in all underlying worksheets, and your drop-down box is in G2 on the summary sheet, you would use this in the summary sheet:

=INDIRECT(" ' "G2" '!" A1)

 

[Caliberjoe]

That would work great but the consignor will not always be in the same cell on each month, i need to be able to select a range of cells for the drop down selection, and then the cell with the sales amount to show on the summary page cell.

Sheets 1-31 have the drop down selection for the consignors in cells B4 thru B25, the sales are listed in C4 thru C25.

On my summary sheet I have the drop down selection in E2. Im trying to make it to where when I select "2102" in E2 on the summary sheet, the sales data from sheet 1 that is listed with that same drop-down selection "2102" will populate in D4.

I guess the hardest part with this is the drop-down selection (2102 in my example) is not always in the same cell throughout the workbook. so lets say on sheet 1 they maybe in B4, however in sheet 2 that same consignor maybe in B20.

I'm not always going to be the one using this workbook so I would like to try and make it as easy for another user as possible.

I need the formula to seek "2102" out from a range of cells, and then correlate the data entered in the cell next to it and relay that to the summary sheet.

 

[Caliberjoe]

no one?

 

[Caliberjoe]

OK better explanation, On summary sheet "consignor" I have a drop down box, with a list of people. in sheets 1-31 I have the same drop down box in B4thruB25. I need to be to link the drop down selection to each page for if a name is selected and the value of the sale in the range C4thruC25 for that name.

The name "2102" May be in B4 on one day, but in another day might be in B25.

I figured I would simplify it by creating a table that shows each day:

So in Colum B I have date, and C is sales.
I am trying to get C4 to pull from sheet one the sales for the name selected at the top of the sheet, so it has to be able to sort through a range of cells in column B and lets say the selected name is in B5 on the first of the month it needs to then give me the data from C5 that has been entered.

I feel like i may have complicated that explanation, or am I going about this the wrong way, is there an easier way to have it pull the info, besides having set spaces for each name ( Ihave over 130 consignors and may sell from a total of 20 of them per day. so want to try and keep this on one page worth per day. I will be saving an electronic version and paper "backup" version. the less paper I use the better lol.)

Any help would be appreciated!

 

[Caliberjoe]

guess i have happened to find the one formula that doesn't exist lol.

 

[Caliberjoe]

I figured it out! SUMIF worked!