I am creating one worksheet for users to use, Labeled 'Test Spreadsheet', building off of other worksheets that have been created. I am trying to find the best option where if one item is selected in column A the second pick, in Column M, corresponds with the value associated with it in the Input Data Sheet. I have tried VLookup but I am needing to actually select the price as somedays will have it but others will not. Any help is greatly appreciated.
I'd go with INDEX(..MATCH(.. or SUMPRODUCT for all of them, but first I'd place your subheaders TS&C or Sunbelt on the same row as each line below it (for example, inserting a column, and putting the subheader in A with the phases in B.
Once you have a set of data that is universally aligned, you could use a single formula in every cell and it would automatically pull from one tab or the other by detecting the header and targeting as needed. Wrap the whole thing in an IFERROR and default to zero when there are no matches, to hide the errors or missing prices.
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
Not certain what you mean about putting subheaders in and where to do so.
Also, I am having issues using the INDEX(MATCH) going between different worksheets.
Now that I think about it, Index/Match will not work either since it will automatically populate every cell that has that equation. Is there a function that I can input that will give me a drop down option to select the corresponding price in the Input data worksheet? Thank you in advance.
You have dates listed in your Summary but not in your input.
Will you eventually be putting dates in your input sheets to pull from, so that everything lines up?
Dropdowns are created by using Data -> Data Validation, choose List and what what you want in that list
No, I will not be inputing dates into the Input Data sheets. Those were created to list the different data and I am using them as the source for my drop downs. I was hoping that if I selected a certain selection in 'TEST SPREADSHEET' in column A, I could then go to that day column M and pick the corresponding $ amount that is listed in one of the Input Data sheets. It doesn't seem like I will be able to without really knowing what I am doing, unless there is a better suggestion. Thank you for the responses.
So to create dropdowns select a cell in A, go to the Data tab, click Data Validation, choose List, and for the list use 'TS&C Input Data'!M3:M49.
The VLOOKUP formulas you have in place should work fine, since you said you don't need to line anything up with date.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
kdb210 
Register To Reply
Bookmarks